How to pass Higher Information Systems Relational Database Systems Using Information Applied Multimedia
Page | i Chapter 1 - Relational Database Systems
A Selection of Key Words
Attribute Anomalies Candidate Key Referential Integrity Query Forms Entity Foreign Key Cardinality Restricted Choice Searching Sub‐forms Flat‐File Surrogate key Normalisation Presence Check Sorting Reports Primary Key Compound Key Entity Integrity Range Check Calculating What is a database?
A database is a collection of related information about a set of persons or objects. Traditionally, databases have been manual paper based systems. An example is the “Yellow Pages” . A database management system (DBMS) is a software package that is used to create, manipulate and present data from electronic databases. Example of DBMSs include Microsoft Access and Filemaker Pro. Flat File Database
The simplest kind of database is a flat file. All data is held in the one file so if a library kept a file of books and borrowers we can get three types of problems. Addition anomalies: If a book is added, it cannot be unless it is being borrowed. If a borrower joins the library, they cannot unless they borrow a book. Deletion Anomalies: If a book is deleted then it may lose the only instance of a borrower and if a borrower leaves then maybe the only record of a book is deleted too (if it has only ever been borrowed by him). Data is very likely to be duplicated. The duplication of data leads to the possibility of data inconsistency. Questions 1. In a relational database model, what is the data a collection of? 2. Describe what is meant by an addition anomaly in a flat file database 3. Describe what is meant by a deletion anomaly in a flat file database 4. Describe what is meant by a duplication anomaly in a flat file database Page | 2 Relational Database
A relational database stores data in more than one table. The idea is to ensure that data is only entered and stored once, so removing the possibility of data duplication and inconsistency. Entities and Data Relationships An entity represents a person or object. e.g. Member, DVD Rental . Each entity has a set of attributes, which describe examples or instances of that entity. • The attributes of the DVD Rental entity are code, title, cost, date out, date due and member number. • The attributes of the Member entity are member number, name and telephone number. Data Relationships • One‐to‐one e.g. one car has a unique registration number • One ‐to‐ Many e.g. one DVD can be borrowed many times • Many‐to‐Many e.g. one pupil has many teachers and one teacher has many pupils. The cardinality is the data relationship between two entities. Keys • A key is a field, or set of fields, whose values uniquely identify a record. • In any table, there may be more than one field or set of fields, which can uniquely identify each record—these are called candidate keys. • The candidate key that is chosen to be used is called the primary key. • A primary key of one entity found in another entity is called a foreign key. • A surrogate key is a key made up when there are too many attributes to make up a unique key. • A compound key is a key made up of two or more attributes. Entity Integrity An attribute cannot exist as a foreign key in one entity unless it already exists as a primary key in another entity. Questions 5.
6.
7.
8.
9.
10.
11.
12.
13.
What is an entity? What is an attribute? What is cardinality? What is a candidate key? For what purpose is a primary key used? How would you describe a foreign key in an entity? Describe what is meant by a surrogate key? What does entity integrity require? What is the name given to a collection of columns that together uniquely identify each row in a table? Page | 3 Normalisation
The process of normalisation takes the data items (called attributes) of the existing entities and produces new entities that are easier to implement in a relational database. Generally, normalisation will produce a final set of “real world” entities such as “Customers”, “Orders” etc. We usually move from a model that is many‐to‐many to one that is one‐to‐many or a mixture of one‐to‐many and one‐to‐one. There are four stages to normalisation, un‐normalised form (UNF), first normal form (1 NF), second normal form (2NF) and third normal form (3NF). Case Study – Caravan Bookings There is a booking form kept in a caravan rental agency. The form records the booking information, Booking Ref, Date of Booking, Name, Address, and Customer Number. The caravan booking details are recorded on the form to allow a customer to book more than one caravan and are Caravan ID, Sleeps, Power, Cost per Week, Date In and Date Out. UNF List all the attributes which must be stored in the database;‐ In the exam, the attributes are Booking Ref Date of Booking listed, usually in order, with any No of days repeated attributes removed Customer name and consistency of attribute Customer Address names like between no. and Customer No number. Caravan ID Sleeps Power Cost per week Date In Date Out 1NF In first normal form, we identify a repeating group and remove it to a new entity. The Caravan ID down to Date UNF 1NF Out is the repeating group. We Booking Ref Booking Ref remove it a new entity. Date of Booking Date of Booking We take the key with it so the No of Days Number of days primary key is repeated and the Customer name Customer name second time it appears it is a Customer Address Customer Address Customer No Customer No foreign key (denoted with *). Caravan ID Caravan ID is the primary key Sleeps along with Booking Ref giving a Caravan ID Booking ref Power compound key. Sleeps Cost per week Power Date In Cost per Week Date Out Page | 4 Date In Date Out 2NF In second Normal form, we have to remove the partial dependencies. In other words, some attributes in the Caravan entity will depend on the Caravan ID and some on the Booking Ref. Booking Ref Booking Ref Copy the top part because we are not going Date of Booking Date of Booking to deal with it just now. Number of Days Number of days Customer name Customer name Write down the two parts of the compound Customer Address Customer Address key and leave some space between them. Customer No Customer No We must decide which of the attributes go with the caravan and which with the booking. Caravan ID Caravan ID Obviously, Sleeps and Power go with caravan Sleeps Sleeps and cost and dates go with the booking. Power Power We now have to look at the keys. Booking Ref has been taken out of the lower entity so Booking Ref *Booking Ref take the key – Caravan ID. To book the Cost per week *Caravan ID caravan we need the Booking Ref, Caravan ID Date In Cost per week and the Date in (the number of days can be Date Out Date In added to this to give the date out. Date Out The Booking Ref is a primary key in the bookings entity and the Caravan ID is a primary key in the Caravan Entity so they become foreign keys in bookings. 3NF In third normal form we remove non‐key dependencies. This means we look at the entities and see if we have a “hidden” repeating group. Customer No Booking Ref We do have a “hidden” repeating group. Customer name Date of Booking The customer name, address and no. will Customer Address Number of days have to be written down every time that Customer name customer makes a booking. They can be Booking Ref Customer Address removed to new entity and Customer No. *Customer No Customer No becomes the key. This leaves Booking Ref, Date of Booking Date of Booking and Number of days but Number of days Caravan ID we need to know who has made the Sleeps booking so we take the customer key with Caravan ID Power Sleeps us. It becomes a foreign key. Power *Booking Ref We now have the entire database system *Caravan ID *Booking Ref in third normal form. Cost per week *Caravan ID Date In Cost per week Date Out Page | 5 Date In Date Out NB Do not repeat any attribute unless making it a foreign key. Do not introduce or add any new attributes to the model. Relationships and Entity Relationship Diagram
We can now establish relationships between the different entities that we have made up. Wherever we see a foreign key, we have a “many” so here we have:‐ • One Customer makes Many Bookings • One Caravan has Many Caravan Bookings • One Booking has Many Caravan bookings We can represent this with an entity relationship diagram. The arrowhead represents Many. Customer Caravans Bookings Caravan Bookings Data Dictionary
A data dictionary holds the information about each entity that you need to help you implement the database system. For our normalised caravans database the data dictionary looks like this. Entity Attribute Key Customer Customer No Customer Name Customer Address Booking Ref Customer No Booking Required Unique Format Validation PK Data Type Number text Y Y Y N text Y N PK FK text number Y Y Y N Lookup from Customer Date of booking No of Days Caravan ID Sleeps Power Booking Ref date Y N PK PK/FK number text number text Y Y Y Y Y N Y N N N Short date integer integer Caravan ID PK/FK text Y N Cost per Week Date In PK number date Y Y N N Date Out date Y N currency Short date Short Caravan Caravan Booking Page | 6 3,7,10,14,21,28 4,6,8 Lookup from Booking Lookup from Caravan >=£50, <=£500 date Note well the following:‐ • Key – PK is unique if not compound key, not unique if compound. • Validation for FK is always “lookup from…” • No of Days and Sleeps will be drop down lists Questions 14.
15.
16.
17.
18.
19.
What is a collection of multi‐valued attributes in an entity referred to? When an entity in UNF is turned into first normal form what is achieved? When an entity in first normal form is turned into second normal form what is achieved? When an entity in second normal form is turned into third normal form what is achieved? What must each column in a database table have? What is special about each row in a database table? Normalisation Question
A holiday club charges members to join and with this money it builds holiday resorts. As it has grown so has its IT systems and now it wants to computerise its booking system. A member can book several holidays each year in apartments owned by the club and consequently each apartment can have up to 52 bookings every year and each member can have several bookings. The booking form was turned into an un‐normalised form (UNF) like this. Booking Ref Member Number Member Name Member Address Member Telephone Number Property Ref Property Name No of Beds No Sleeps Date in No of Nights User Charge Using the Un‐normalised data form:‐ 1. Turn the Un‐normalised form into first normal for. 2. Turn the first normal form into the second normal form 3. Turn the second normal form into the third normal form. 4. Create an entity relational diagram Page | 7 Developing a Solution
Referential Integrity – a foreign key must always refer to a record that exists in another table. It is established by defining relationships between the tables. Validation – can be established in three different ways. •
•
•
Presence Check – Where you have said Required = Yes Range Check – Where the validation looks for a range of data e.g. between £50 and £500 Restricted Choice – Choice between several items e.g. Mr, Mrs, Miss, Dr. Format • Numbers can be formatted as Integer, Real, Currency etc. • Date & Time can have many formats including Short date (4/12/10), long date (4th December 2010). Time can be 24‐hour clock, am/pm and so on. Queries Queries are used to interrogate your database. Within a query, you can:‐ •
•
•
Search for specific records Sort groups of selected records Perform calculations on selected records. Searching Searching is the process of selecting records from a table or combination of tables. To perform the query, three items must be identified •
•
•
Which fields will be used to identify the records required? What are the criteria for identifying the records required? Which fields will be displayed? Sorting To perform a sort, two items must be identified: •
•
Which field (or fields) will be used to decide the order of records? For each field selected, will the order of sorting be ascending or descending? Calculations Horizontal calculations are often known as calculated fields, and vertical calculations are known as summary fields. Here is a summary of the commonly used functions that are used to build calculations. •
•
•
•
•
Aggregate – Sum, Average, Maximum, Minimum, Count Mathematical – Sun, Cos, Tan, Integer, Round Text, ‐ Left, Right, Middle, Length, Uppercase, Lowercase, Find, Replace Logical – If, IsNull, IsNumeric, IsError Conversion – Number‐to‐text, Text‐to‐Number, Date‐to‐Text, Text‐to‐Date Page | 8 Macros Can be used to add control. Scripted using predefined words and functions. E.g., open a form from a form based on a condition. Forms Forms are used to create a user interface that is better than seeing tables and several different forms can access the same table allowing different views of the table. Sub forms can be added to a form to show a related “many” table with is originating table. Reports Reports are used to allow us to produce printed copy in an atheistic pleasing manner. A form or report is usually based on a query, which selects the required fields from the appropriate tables, sorting the results if necessary, and performing any horizontal calculations. Questions 20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
What is special about each row in a database table? What is the purpose of the data dictionary? What should data dictionaries include details of? What does referential integrity require? Describe the three different types of data validation. Name three ways in which numbers can be formatted. In a relational database system what is a query? In a relational database system what is a form? In a relational database system what is a report? In a relational database system what is a macro? Page | 9