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 i