Database Concepts

Database (Data manipulation)

What is a database?

A database: is an organised collection of data. A database program is software which stores and retrieves data in a structured way. This includes the data that is stored and the links between the data items.

All databases store data using a system of files, records and fields:

A field: is a single item of data. Each field has a field name that is used to identify it within the database. Each field contains one type of data.

A record: is a collection of fields. These may contain different data types.

A file: is an organised collection of records.

Primary key/Key field: A primary key is a field that uniquely identifies a record and may never be repeated for more than one record.

Foreign key:

A foreign key in one table will point to a primary key in another table. A table may have multiple foreign keys.

 

Relationships

A relationship is a term used with relational databases to indicate a link or association between fields. The relationship between the tables can be:

• one-to-one

Like the relationship between a person and their ID card. One person has only one ID card and the ID card is associated only to one person.

• one-to-may

Like the relationship between a father and his children. One father has many children but each child only has one father.

• many-to-many

Like the relationship between students and courses. One student can take several courses and each course can be taken by several students. Thus the relationship is many to many.

 

Types of database:

Databases have two types: flat-file databases and relational databases:

1. Flat-file databases:

A flat-file system refers to a database where all the data is held in a single table or file. An example of a flat file database is given below

 

Employee’s data table:

Emp_code	Fir_name	Las_name	Gender	DOB	Branch	Branch-address
1	Mohamed	Kamal	Male	01/01/1960	Cairo	15 Horya St.
2	Ali	Khalifa	Male	03/07/1970	Alexandria	5 Manshia Sq.
3	Sally	Gomaa	Female	13/05/1971	Ismailia	7 M. Ali St.
4	Yasser	Farag	Male	04/01/1965	Cairo	15 Horya St.
5	Khaled	Hassan	Male	05/05/1957	Ismailia	7 M. Ali St.

 Notice: the Branches data are duplicated.

 

2. Relational databases:

A relational database stores data in more than one linked table. Relational databases are designed so that the same data is not stored many times. Each table within a relational database will have a primary key. Most tables will have a primary key field that holds unique data (no two records are the same in this field) and is the field used to identify this record.

 

Example:

Employee’s data table:

Emp_code	Fir_name	Las_name	Gender	DOB	Bran_code
1	Mohamed	Kamal	Male	01/01/1960	1
2	Ali	Khalifa	Male	03/07/1970	2
3	Sally	Gomaa	Female	13/05/1971	3
4	Yasser	Farag	Male	04/01/1965	1
5	Khaled	Hassan	Male	05/05/1957	3

 

Branches’ data table:

Bran_code	Branch	Branch-address
1	Cairo	15 Horya St.
2	Alexandria	5 Manshia Sq.
3	Ismailia	7 M. Ali St.

Creating a relationship between the two tables:

Notice:

• Bran_code field is a primary key in the Branches table, so can’t be duplicated.
• Bran_code field is a foreign key in the Employees table, so can be duplicated.

 

Advantages of relational database over flat files:

1. Data is not repeated so less storage capacity is used.
2. Data is not repeated so each change to an item of data has to be made only once.
3. It is easier for users to produce reports from a relational database, where data is held in two or more tables, than from two or more flat-file databases.

Searching using wildcards:

A wildcard is a character that is used as a substitute for other characters. The * (asterisk) character to show a number of characters (including 0), while the ? (question mark) to show a single character.

Key features of a well-designed form:

• A title that states what data is being collected
• Instructions on filling the form
• Use of descriptive data labels rather than short field names, such as use of Student name rather than S_name
• Appropriate space for each field for the data that will be added
• Appropriate space between each field
• Radio buttons (or drop down menus) are used where possible
• Navigation buttons on the form to allow a user to add new records and move between records
• Important data like the key field can be highlighted to show that this data must be completed before the record can be saved
• The form fills available space (window)
• Se of legible font size in the form

Data types:

Before we enter data into a computer system, we usually need to tell the computer what type of data it is. This is because the computer stores and processes different types of data in different ways.

1. Numeric Data:

Numeric data simply means numbers. However, numbers come in a variety of different types.

• Integers

An integer is a whole number – it has no decimal or fractional parts. Integers can be either positive or negative. Examples: 12, 45, 1274, 1000000, -3, -5735.

• Real Numbers

Real numbers include whole numbers (integers) and numbers with decimal/fractional parts. Real numbers can be positive or negative. Examples: 1, 1.4534, 946.5, -0.0003, 3.142.

• Currency

Currency refers to real numbers that are preceded by a currency symbol. Examples: £12.45, -£0.01, €999.00, $5500.

• Percentage

Percentage refers to fractional real numbers that are followed by a percent symbol. So, the real value 0.5 would be shown as 50%, the value 0.01 would be shown as 1% and the number 1.25 would be shown as 125%. Inside the computer the 50% is stored as a real number: 0.5, But when it is displayed it is shown formatted as a percentage. Examples: 100%, 25%, 1200%, -5%. 2.

2. Alphanumeric/Text Data

Alphanumeric (often simply called ‘text’) data refers to data made up of letters (alphabet) and numbers (numeric). Usually symbols ($%^+@, etc.) and spaces are also allowed. Examples: CAT, “A little mouse”, ABC123, adam_am@yahoo.com

3. Date/Time Data

Date (or time) data is usually formatted in a specific way. The format depends upon the setup of the computer, the software in use and the user’s preferences. Date Examples: 25/10/2007, 12 Mar 2008, 10-06-08. Time Examples: 11am, 15:00, 3:00pm, 17:05:45.

4. Boolean/Logical Data

Boolean data is sometimes called ‘logical1 data (or in some software, ‘Yes/No’ data). Boolean data can only have two values: TRUE or FALSE. Note that TRUE and FALSE can also be shown as YES / NO, ON / OFF, or even graphically as tick boxes (ticked / unticked). Examples: TRUE, FALSE, YES, NO, ON, OFF.

Selecting Data Types

When we are presented with data to be input into a computer system, we must analyze it and select appropriate data types for each value.

Student Name :	Adam Amr	Alphanumeric
Student Number:	1255	Numeric (integer)
Date of Birth:	15 June 2010	Numeric (Date)
Year Group:	3	Numeric (Integer)
Telephone:	(02064) 3338333	Alphanumeric
Special Diet:	No	Boolean
Exam Score:	95%	Numeric (Percentage)
Height:	1.65	Numeric (Real)
Fees Paid:	$1300	Numeric (Currency)

Note that the telephone number in the example is alphanumeric/text data. You might think that it should be numeric, however phone numbers often have spaces, dashes, etc. which numeric data cannot have.

 

Data Validation

Validation is a process where data is checked to see if it satisfies certain criteria when input into a computer. A number of validation techniques exist.

 

Validation check	Description	Example(s)
Range check	Checks whether data is within given/acceptable values	e.g. To check if a person’s age is > 0 but is also < 150
Length check	Checks if the input data contains the required number of characters	e.g. If a field needs six digits then inputting a five-digit or seven-digit number should cause an error message
Type check	Checks that the input data has the appropriate data type	e.g. A person’s age should be numeric integer not alphanumeric, for example
Format check	Checks the data is in a specific format or fixed pattern	e.g. A date which should be in the form of dd/mm/yyyy
Presence check	Checks if data is actually present and hasn’t been missed out	e.g. In an electronic from a person’s telephone number may be a required field so, if no data is entered, an error message will be produced
Existence check	Checks if data from a list has been chosen and not left blank	e.g. When signing up to a social networking website, the country list may contain existence check. If the user doesn’t choose a country from the list then an error message will be displayed
Boolean check	Checks that the input data is from only two options.	e.g. If a database admin wishes to restrict the users to enter only ‘M’ or ‘F’ in the Gender field, then the admin should put Boolean check on the Gender field that accepts only characters ‘M’ or ‘F’
Check digit	A validation check used in barcodes to validate the correctness of the barcode. This is done by adding an extra digit to the left that is calculated from the rightmost digits using some formula.	e.g. Validating the correctness of a barcode

 

Data Verification

Verification is a way of preventing errors when data is copied from one medium to another. There are two common ways that verification checks are carried out:

• Visual check

This is checking for errors by the person who is entering the data. They compare the entered data with the original document (i.e, what is on the screen is compared to the data on the original paper documents – note that this is not the same as proofreading).

• Double data entry

In this method, data is entered twice, using two people, and is then compared (either after data entry or during the data-entry process). 5.6 Data Validation Validation is a process where data is checked to see if it satisfies certain criteria when input into a computer. A number of validation techniques exist.

 

Testing data

Testing data is the data entered to the computer to check that the computer program (the database in our case) will respond correctly to correct and incorrect data. Testing data has three main categories:

• Normal data

This is data that is acceptable/reasonable and has an expected outcome. For example, the month can be any whole number in the range of 1 to 12.

• Abnormal data

This is data outside the limits of acceptability, or wrong type of data, and should be rejected or cause an error message. For example, all the following values are not allowed as inputs for the month:

• • Negative numbers.
  • Any value greater than 12.
  • Letters or non-numeric data.
  • Non-integer values (e.g., 3.5,10.75, etc.).

• Extreme data

This is data at the limits or acceptability. For example, the extreme values of month can be either 1 or 12.