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 dup