VJC Chapter 19 Databases

1 Chapter 19 Databases Contents 1 How is data stored? 2 Data redundancy 3 Relational database 4 Normalisation 5 Entity-Relationship diagram Syllabus Learning Outcomes 3.3 Databases and Data Management Understand, create and use SQL and NoSQL databases, as well as understand techniques to protect the privacy and integrity of data. 3.3.1 Determine the attributes of a database: table, record and field. 3.3.2 Explain the purpose of and use primary, secondary, composite and foreign keys in tables. 3.3.3 Explain with examples, the concept of data redundancy and data dependency. 3.3.4 Reduce data redundancy to third normal form (3NF). 3.3.5 Draw entity-relationship (ER) diagrams to show the relationship between tables. 3.3.6* Understand how NoSQL database management system addresses the shortcomings of relational database management system (SQL). 3.3.7* Explain the applications of SQL and NoSQL. 3.3.8* Use a programming language to work with both SQL and NoSQL databases. *Note: NoSQL will be addressed in later chapter

2 1 How is data stored? Data can be stored in a flat file 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. A flat file database is a database that contains a single table. A table contains a collection of records for a particular theme. In the Student table above, it is a collection of student data from a civics class. A record (a row in a table) is a complete set of data about a single item. In the table above, each record contains the data of one student. A table has a number of fields. A column in a table is called a field. Attributes are the describing characteristics or properties that define all items pertaining to a certain category applied to all cells of a column. In the example above, the table has 10 records and 5 fields, and the attributes are RegNo, Name, Gender, CivicsClass and CivicsTutor. Are there any potential issues that might arise from storing the data in a flat file database? Peter Lim is the Civics Tutor for the Civics Class 18S12. In a flat file database, his details would need to be entered many times as long as a student is in the class 18S12. This is known as data redundancy. Record Field

3 2 Data redundancy Data redundancy refers to the same data being stored more than once. Having to enter data multiple times means the database is at an increased risk of having inaccurate data. It would be hard to update because every occurrence of the data item will need to be changed. This can lead to data inconsistency. This will cause issues when inserting, updating and deleting data from the database. Referring to the Student table above, see below for possible issues. Inserting data A new student from the same Civics Class to be inserted will need to insert duplicated data on the Civics tutor. Updating data If Peter Lim decide to leave the college, all the records with his name as the Civics Tutor 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. To address this issue, a more efficient way of storing data has to be adopted.

4 3 Relational database The most common model for a database is a relational database model. A relational database is a collection of relational tables where data are organised in one or more tables with relationships between them. In general, a table in a relational database can be expressed as: TableName (Attribute1, Attribute2, Attribute3, …) For example, Student (RegNo, Name, Gender, CivicsClass, CivicsTutor) A table needs to fulfil the following conditions: ● Values are atomic (Information in each column cannot be an array or another table, it only contains one piece of information which cannot be broken down further.) ● Columns are of the same kind ● Rows are unique ● The order of columns is insignificant ● Each column must have a unique name It is important to be able to identify each record in table uniquely using a key field. A key field, or key in short, is a combination of one or more columns in a database that uniquely identifies a row in a table. Keys allows for the establishment of relationships between tables and allows for the identification of relation between tables. Keys also help to enforce identity and integrity in the relationship. There are different type of keys. 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 field or a set of fields in a table whose values 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. It is also a candidate key that is most appropria te to become the main key for a table. Secondary keys

5 Candidate keys that are not selected as primary key are known as secondary keys. A secondary key is an additional key, or alternate key, which can be used in addition to the primary key to locate specific data. In the given table above, RegNo, Name, and Email are candidate keys which help us to uniquely identify the student record in the table. Any one of the candidate keys can be selected as the primary key, for example, RegNo. The rest of the two keys would be the secondary keys. Composite primary keys Sometimes, more than one field is needed to uniquely identify a record. A composite primary 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. RegNo Name CivicsClass 1 Adam 18A10 2 Adrian 18A10 1 Adam 18S12 2 Bala 18S12 In the example above, both RegNo and CivicsClass are needed to form a composite key to uniquely identify a record. Foreign keys A foreign key is an attribute (field) in one table that refers to the primary key in another table. It links to a primary key in the other table and form a relationship between the tables. RegNo Name Email 1 Adam adam@gmail.com 2 Adrian adrian@gmail.com 3 Agnes agnes@gmail.com 4 Aisha aisha@gmail.com Candidate keys Primary key Secondary keys Composite keys

6 Another table, ClassInfo, as shown below, stores the information about each civics class. Notice that the CivicsClass field is the primary key in the table ClassInfo and is related or linked to the CivicsClass field in table Student. This makes CivicsClass field in table Student a foreign key (FK). One benefit of a relational database is that all data can be updated at the original source. Data doesn’t need to be repeated and relationships can be made using the source table’s unique identifier. 4 Normalisation Normalising the tables in the relational database is an important process of organising the tables so as 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). You might want to take a quick look at this article before reading on: https://www.freecodecamp.org/news/database-normalization-1nf-2nf-3nf-table- examples/ First Normal Form For a table to be in 1NF, all columns must be atomic. This means ther