Database Terms t DBMS Database Management System A

Database Terms t DBMS – Database Management System. A software used to organise, analyse, store, retrieve, and edit information. – e. g. , Visual Fox. Pro, Access

Database Terms t Database – collection of data organised for storage in a computer memory. In relational database, information are stored in the form of table.

Database Terms t Record – a unit of storage in a database. In relational database, the rows of table represent records.

Database Terms t Field – a data unit in a record. In relational database, the columns of table represent fields. Since fields have different characteristics (numeric, character, date, etc. ), they are also called “attributes”.

Database Terms t Key – a KEY is used to uniquely identify a record in the database. – a KEY can be a single field or combination of fields

Database Field Key Record stud_id name dob F 00111 Winnie {02/14/83} F 00112 Amy {03/16/82} F 00007 Steve {10/28/83} F 00246 Amy {08/25/82}

Database Design t Aims – to keep data integrity (correctness and completeness) with the least for • space • data duplication • updating efforts

Database Design t Basic design – there must exist a KEY that uniquely determines the entire record – e. g. , student identity that uniquely determines a student record

Sample Address Book Name: Tele: Address:

Sample Address Book ADDRBOOK. DBF Field name Data type Width Name Char. 15 Tele Char. 8 Address Char. 40

Sample Student Report Student ID: f 99007 Student Name: Wong Siu Fu Average Mark: 60. 25 Class: 6 F Subject Code Subject Description. Marks C 01 Chinese 64. 00 E 01 English 62. 00 M 01. . . Mathematics. . . 86. 00. . .

Sample Student Report SREPORT. DBF Field name Data type Width stud_id Char. 6 name Char. 25 class Char. 2 sub_code Char. 3 sub_desc Char. 15 marks Numeric 3 ave_mk Numeric 6 (2 d. p. )

Sample Student Report SREPORT(stud_id, name, class, sub_code, sub_desc, marks, ave_mk)

Sample Student Report Student ID: f 99008 Student Name: Po King Wan Average Mark: 60. 25 Class: 6 F Subject Code Subject Description. Marks C 01 Chinese 66. 00 E 01 English 60. 00 M 01. . . Mathematics. . . 72. 00. . .

Database Terms t Data Redundancy (冗餘數據) – same data item exists in one or more databases. – Disadvantage: • duplication causes wasted storage space • efforts to maintain common data up-to-date

Database Terms t Data Integrity (數據完整性) – refers to the correctness and completeness of information in the database.

Data Normalisation t a step-by-step process that is used to decompose a database into two or more databases t in order to avoid side effects during the operation of a database involving insertion, deletion and updating records.

Data Normalisation t First Normal Form (1 NF) t Second Normal Form (2 NF) t Third Normal Form (3 NF)

Data Normalisation t First Normal Form (1 NF) – if every attribute is based upon a simple, unique value, i. e. , there are no repeating groups of attribute types.

Sample Student Report SREPORT(stud_id, name, class, (sub_code, sub_desc, marks), ave_mk)

Data Normalisation Deriving 1 NF SREPORT(stud_id, name, class, ave_mk) MARKS(stud_id, sub_code, sub_desc, marks)

Data Normalisation t Second Normal Form (2 NF) – it is already in 1 NF and if each non-key attribute depends fully upon the key.

Data Normalisation SREPORT(stud_id, name, class, ave_mk) MARKS(stud_id, sub_code, sub_desc, marks)

Data Normalisation Deriving 2 NF SREPORT(stud_id, name, class, ave_mk) MARKS(stud_id, sub_code, marks) SUBJECT(sub_code, sub_desc)

Data Normalisation t Third Normal Form (3 NF) – it is already in 2 NF and if there is no dependency between non-key attribute.

Data Normalisation Deriving 3 NF SREPORT(stud_id, name, class, ave_mk) MARKS(stud_id, sub_code, marks) SUBJECT(sub_code, sub_desc)

Sample Invoice INVOICE NUMBER: 100486 -300 Customer Number: C 12345 Customer Address: Hang Shin Link Invoice Date: 04/01/99 Item Code Item Desc. Item Price Quantity Price 1123 Chocolate 8. 00 2 16. 00 1246 Coke 5. 00 12 60. 00 Total: 76. 00

Data Normalisation Invoice(Inv_no, Cust_Addr, Inv_date, (Item_code, Item_desc, Item_price, Qty, Price), Total)

Data Normalisation Deriving 1 NF Invoice(Inv_no, Cust_Addr, Inv_date, Total) Item. Details(Inv_no, Item_code, Item_desc, Item_price, Qty, Price)

Data Normalisation Deriving 2 NF Invoice(Inv_no, Cust_Addr, Inv_date, Total) Item. Details(Inv_no, Item_code, Qty, Price) Items(Item_code, Item_desc, Item_price)

Data Normalisation Deriving 3 NF Invoice(Inv_no, Cust_no, Inv_date, Total) Customer(Cust_no, Cust_Addr) Item. Details(Inv_no, Item_code, Qty, Price) Items(Item_code, Item_desc, Item_price)

Partial Dependence Item. Details(Inv_no, Item_code, Item_desc, Item_price, Qty, Price) The non-key fields “Item_desc” and “Item_price” is dependent partly on the key, i. e. , “Item_code”. This is known as partial dependence.

Transitive Dependence Invoice(Inv_no, Cust_Addr, Inv_date, Total) The non-key field “Cust_Addr” is dependent on the non-key field “Cust_no”, this is known as transitive dependence.

Database Terms t Data Independence – refers to the data in a database which is separated from the application programs that manipulate it.