HCI SQL Databases 1 - Introduction to Databases

SQL Databases CPDD Computer Education Unit Version: Feb 2019 1 Name: __________________________ ( ) Class: _________ Date: _________ Lesson 1: Introduction to Databases Instructional Objectives: By the end of this task, you should be able to:  Draw entity-relationship (ER) diagrams to show the relationshi p between tables.  Determine the attributes of a database: table, record and fiel d.  Explain the purpose of and use primary, secondary, composite a nd foreign keys in tables.  Explain with examples, the concept of data redundancy and data dependency.  Reduce data redundancy to third normal form (3NF).

SQL Databases CPDD Computer Education Unit Version: Feb 2019 2 What is a database? A database is a collection of data stored in an organised or logical manner. Storing data in a database allows us to access and manage the data. Examples of databases in real-life include:  Patient medical records  Supermarket inventory  Contact list Relational database In the relational database model, data is stored in relations and represented in the form of tuples (rows). A relational database is a collection of relational tables. A table is a two-dimensional representation of data stored in rows and columns. A table description can be expressed as:   TableName (Attribute1, Attribute2, Attribute3, …)  For example, Student (RegNo, Name, Gender, CivicsClass, CivicsTutor) A table is a relation if it fulfils the following conditions:  Values are atomic  Columns are of the same kind  Rows are unique  The order of columns is insignificant  Each column must have a unique name Below is an example of a table Student18S12, showing data for students from civics class 18S12. Field  Record

SQL Databases CPDD Computer Education Unit Version: Feb 2019 3 Tables are made up of records and fields. A record is a complete set of data about a single item. For this example, a record refers to the complete set of data of a particular student. A field refers to one piece of data about a single item. For this example, the table has 4 fields – RegNo, Name, Gender and MobileNo. Candidate keys A candidate key is defined as a minimal set of fields which can uniquely identify each record in a table. It tells a particular record apart from another record. A candidate key should never be NULL or empty. There can be more than one candidate key for a table. A candidate key can also be a combination of more than one field. Primary keys A primary key is a candidate key that is most appropriate to become the main key for a table. It uniquely identifies each record in a table and should not change over time. That is, a primary key tells a particular record apart from another record. Secondary keys Candidate keys that are not selected as primary key are known as secondary keys. Composite keys Sometimes, more than one field is needed to uniquely identify a record. A composite key is a combination of two or more fields in a table that can be used to uniquely identify each record in a table. Uniqueness is only guaranteed when the fields are combined. When taken individually, the fields do not guarantee uniqueness. Take a look at the table Student on the next page. Test your understanding How many records does the table Student18S12 have? Test your understanding Which of the candidate key is a suitable primary key for the table Student18S12? Test your understanding Which of the fields in table Student18S12 are candidate keys?

SQL Databases CPDD Computer Education Unit Version: Feb 2019 4   Now, a single field is not able to uniquely identify a record. A composite key composing 2 fields is needed uniquely identify a record. Foreign keys Another table, ClassInfo, as shown below, stores the information about each civics class.   CivicsClass is chosen to be the primary key (PK) for table ClassInfo. Notice that the primary key in table ClassInfo (i.e., the CivicsClass field) is related or linked to the CivicsClass field in table Student. This makes CivicsClass field in table Student a foreign key (FK). Test your understanding Which 2 fields would you choose to form a composite key for the above table Student? CivicsClass 18S12 CivicsClass 18A10

SQL Databases CPDD Computer Education Unit Version: Feb 2019 5 ClassInfo CivicsClass (PK)  CivicsTutor HomeRoom A foreign key is an attribute (field) in one table that refers to the primary key in another table. Data redundancy Data redundancy refers to the same data being stored more than once. Below is an example of Student table. As we can see, data for CivicsClass and CivicsTutor are repeated for students who are in the same civics class. This is data redundancy. This will cause issues when inserting, updating and deleting data from the database. See the table below for examples. Inserting data A new student cannot be inserted unless a civics class and a civics tutor have been assigned. Updating data If Peter Lim decide to leave the college, all the records in the Student table would need to be updated. If we miss any record, it will lead to inconsistent data. Deleting data If all the records of the Student table are deleted, information on civics class and civics tutor will be lost. Data dependency In order to normalise data, we need to understand data dependencies, specifically functional and transitive dependencies. Student RegNo  Name Gender CivicsClass (FK)

SQL Databases CPDD Computer Education Unit Version: Feb 2019 6 Functional dependency Attribute Y is functionally dependent on attribute X (usually the primary key), if for every valid instance of X, the value of X uniquely determines the value of Y (X  Y). For example, suppose we have a Student table with the following attributes: MatricNo, Name, Gender, CivicsClass and CivicsTutor. MatricNo is a unique number assigned to every student in the college. MatricNo uniquely identifies the Name because if we know the MatricNo, we can know the Name associated with it. Therefore, we can say Name is functionally dependent on MatricNo (MatricNo  Name). Transistive dependency A functional dependency is said to be transitive if it is indirectly formed by two functional dependencies. Z is transitively dependent on X if Y is functionally dependent on X but X is not functionally dependent on Y, and Z is functionally dependent on Y. In other words, X  Z is a transitive dependency if the following hold true:  X  Y  Y d o e s n o t  X  Y  Z For example, CivicsClass is functionally dependent on MatricNo (MatricNo  CivicsClass) but MatricNo is not functionally dependent on CivicsClass. On the other hand, CivicsTutor is functionally dependent on CivicsClass (CivicsClass   CivicsTutor). Therefore, CivicsTutor is transitively dependent on MatricNo  (MatricNo  CivicsTutor).  Normalisation Normalisation is the process of organising the tables in a database to reduce data redundancy and prevent inconsistent data. There are at least three normal forms associated with normalisation: first normal form (1NF), second normal form (2NF), and third normal form (3NF). First Normal Form For a table to be in 1NF, all columns must be atomic. This means there can be no multi-valued columns – i.e., columns that would hold a collection such as an array or another table. In another words, the information in each column cannot be broken down further. Consider the following table: It is assumed that every civics class only has one civics tutor, and each CCA has only one teacher in-charge (IC).

SQL Databases CPDD Computer Education Unit Version: Feb 2019 7 This table is not in 1NF because the CCAInfo column contains multiple values. In order for the table to be in 1NF, we split CCAInfo into two single-value column