9.1 Databases

1) Single-table database (Flat database):

A simple database that stores all data in a single table in rows and columns.

It contains unrelated tables like a spreadsheet. It can be converted into a text file separating each field using delimiters like comma, hyphen, etc.

2) Relational database:

A computerized record-keeping system that contains two or more tables of data, interrelated through key fields called relationships.

Relational databases are very flexible for accessing, managing, and updating data.

3) Distributed database:

A relational database distributed over a computer network, allowing multiple users to access data simultaneously.

★ Advantages

• Avoids data redundancy (duplication of data).
• Increased data consistency (correctness) — allows only valid data defined by some rules.
• Improved data access through use of host and query languages.
• Improved data security through user credentials.
• A database can be shared with many users over the network.

★ Disadvantages

• Database systems are complex, difficult and time-consuming to design.
• Extensive conversion costs in moving from a file-based system to a database system.
• Initial training required for all programmers and users.

(a) Database Management System (DBMS):

A DBMS is a set of programs that allows the creation of data files called tables and permits the ways to process, alter and extract records.

It provides the following features:

• Allows the data to be stored by creating different tables which could be linked together.
• Allows to search records which meet certain criteria (conditions) using a Query.
• Allows editing and deletion of records.
• Allows the alteration of structure of the table.
• Allows import and export of data.
• Allows to set security to access the data.

(b) Four main components of a DBMS:

★ Table: A data structure and the basic unit of storing data in a database. It stores related data in rows (records) with different pieces of information in columns (fields) of a record.

★ Form: A database object created for the user to enter, edit or display data from the data source table. It provides a user-friendly interface for a database application.

★ Query: A request used to extract data from a database according to the user's request. It uses Structured Query Language (SQL) that can be written or designed through query design grids.

★ Report: A database object used to display and print formatted data in an organized manner from the database table.

(a) Record and Field:

Record: A basic data structure composed of different fields in a row. Each record is a single row in the table that contains different information about an item. A set of records makes a database table.

Field: The columns, or data categories of the same data type, used by each record. It represents an attribute (a characteristic or quality) of some entity (object, person, place, or event).

Each row is a record; each column is a field. The whole grid is a table.

(b) Primary Key-field:

A primary key is a field in a table which uniquely identifies each record in a database table.

• Primary keys must contain unique values.
• A primary key column cannot have NULL values or be left empty.

(c) Purposes of primary key-field:

• It serves as a unique identifier for a row of data in a database table.
• It stops the possibility of duplicate data.
• It helps to speed up queries, searches and sort requests.
• It is used to establish relationships with other tables by linking the fields.

(a) Different data types used in a table:

(b) Two reasons why choosing the correct data type is important:

1. To do a validation check over data while entering, to prevent invalid characters being stored.
2. To check the correct format of data, like date and time, to be stored in a uniform way.

(c) Two reasons why setting the field size / max length is important:

1. File size can be kept as small as possible.
2. Time taken to process data can be kept minimum.

(a) Why coding data is useful:

• Codes are quicker to type in.
• Using codes reduces the size of the files.
• Increases the speed of search and process performed on the data.
• Codes are often unique and are easy to search.

(b) Three factors when designing codes:

1. Code should always be of same length.
2. Codes should contain enough detail and should not be too short.
3. Codes should be easy to use.

(a) Data Validation:

Validation is an automatic computer check to ensure that the data entered is sensible, reasonable and consistent. Data is accepted only if it falls within the set rules.

Note: Validation does not check the accuracy of data — only that it is sensible.

(b) Validation checks:

(a) SQL query:

SQL stands for Structured Query Language. It is a standard query language for writing scripts, to make requests to the database for the records which meet the criteria we specify.

An SQL script is a list of SQL commands that performs a given task, often stored in a file so the script can be reused.

(b) Benefits of using a query:

1. View data only from the fields you are interested in viewing.
2. Combine data from several data sources.
3. Use expressions as fields.
4. View records that meet criteria that you specify.

(c) Function of each SQL statement:

(i) SELECT — Fetches specified fields from the table; queries always begin with SELECT.

SELECT Field1, Field2, Field3   -- specifies individual fields
SELECT *                       -- specifies ALL fields

(ii) FROM — Identifies the table to use.

FROM TableName   -- specifies the table to use

(iii) WHERE — Includes only records in a query that match a given condition.

WHERE Condition   -- values must match the field's data type

(iv) ORDER BY — Sorts the results by a given column, alphabetically or numerically.

ORDER BY Field1, Field2                    -- ascending
ORDER BY Field1 DESC, Field2             -- first field descending, second ascending

Note: DESC specifies descending order only. To sort in ascending order, omit the keyword.

(v) SUM — Returns the sum of all values in a field. Used with SELECT.

SELECT SUM(Field)               -- sum of values in field (must be integer/real)
SELECT SUM(Field) AS ResultField  -- alias name is optional

(vi) COUNT — Counts the number of records where the field matches a condition. Used with SELECT.

SELECT COUNT(Field)               -- counts items matching criteria
SELECT COUNT(Field) AS ResultField -- alias name is optional

(a)(i) Records and fields:

Records: 10  ·  Fields: 9 (StdID, StdName, Sex, DOB, Grade, House, Eng, Dhi, Math)

(a)(ii) Primary key field and reason:

StdID — because the field contains unique data elements which can be used to identify each record (all data elements are different).

(b) Data type for each field:

(c) Display StdID, StdName, Sex in ascending order of name, grouped by sex (M first, F next), where Eng > 60:

SELECT StdID, StdName, Sex
FROM MARKS
WHERE Eng > 60
ORDER BY Sex DESC, StdName ;

Note: The field that has to be grouped should be sorted first. M sorts after F alphabetically, so DESC puts M first.

Output:

(d) Display name and Math marks of all girls of Grade 10(A) in descending order of Math marks:

SELECT StdName, Math
FROM MARKS
WHERE Sex Like "F" AND Grade Like "10(A)"
ORDER BY Math DESC ;

(e) Display StdID, StdName, Grade in descending order of Dhi marks grouped by Grade, where Dhi < 45 OR Dhi >= 60:

SELECT StdID, StdName, Grade
FROM MARKS
WHERE Dhi < 45 OR Dhi >= 60
ORDER BY Grade, Dhi DESC ;

(f) Display StdID, StdName, Sex in descending order of Eng marks grouped by gender (F first, M next), where Eng between 60 and 75 (inclusive):

SELECT StdID, StdName, Sex
FROM MARKS
WHERE Eng >= 60 AND Eng <= 75
ORDER BY Sex, Eng DESC ;

Equivalent: WHERE Eng Between 60 and 75

(g) Display StdName only in descending order of Eng marks grouped by Grade, where Eng >= 50 OR Dhi > 60:

SELECT StdName
FROM MARKS
WHERE Eng >= 50 OR Dhi > 60
ORDER BY Grade, Eng DESC ;

(h) Display StdName and Grade where student has passed all three subjects with marks >= 60:

SELECT StdName, Grade
FROM MARKS
WHERE Eng >= 60 AND Dhi >= 60 AND Math >= 60 ;

(i) Display StdName and Grade where Math >= 85 AND (Eng >= 60 OR Dhi >= 60):

SELECT StdName, Grade
FROM MARKS
WHERE Math >= 85 AND (Eng >= 60 OR Dhi >= 60) ;

(j) Display StdID, StdName, Grade for students belonging to a House other than Orange:

SELECT StdID, StdName, Grade
FROM MARKS
WHERE House Not Like "Orange" ;

(k) Display StdID, StdName, Grade for students belonging to Red OR Blue house:

SELECT StdID, StdName, Grade
FROM MARKS
WHERE House Like "Red" OR House Like "Blue" ;

(l) Display StdName, Grade, DOB for students born between 1st June 2004 and 30th October 2004:

SELECT StdName, Grade, DOB
FROM MARKS
WHERE DOB >= #6/1/2004# AND DOB <= #10/30/2004# ;

Equivalent: WHERE DOB Between #6/1/2004# AND #10/30/2004#

(m) Display StdName, Sex, total mark (Eng+Dhi+Math) for Grade 10(A) students in highest-to-lowest order of total:

SELECT StdName, Sex, (Eng + Dhi + Math) AS TotalMark
FROM MARKS
WHERE Grade Like "10(A)"
ORDER BY (Eng + Dhi + Math) DESC ;

(a) Why ItemNo could not be a primary key:

All fields contain repeated data elements (e.g. CH001 appears in rows 1 and 3; TB003 in rows 2 and 8). So no single field can uniquely identify each record, and none can be set as the primary key field.

(b) Total amount collected from Delivered items:

SELECT SUM(Amount) AS TotalAmt
FROM SALES
WHERE Status Like "Delivered" ;

Output:

(c) Count items "In progress":

SELECT COUNT(Status) AS NoItems
FROM SALES
WHERE Status Like "In progress" ;

Output:

(d) Count items with Amount > 3500:

SELECT COUNT(Amount) AS NoItems
FROM SALES
WHERE Amount > 3500 ;

Output: