260 likes | 387 Views
Database Management Systems. Session 5. Objectives. Understand the structure of a data table Understand data normalisation Understand and use the terms: - entity - attribute - field - record - primary key Understand the principle of linking tables in a relational database. Database Tables
E N D
Database Management Systems Session 5
Objectives • Understand the structure of a data table • Understand data normalisation • Understand and use the terms: - entity- attribute- field- record- primary key • Understand the principle of linking tables in a relational database
Database Tables Normalisation Linking Tables Database Schema Topics
Record Gertrude Jones Name Field Address 34 Great West RoadLondon W23 8RT 020 8564 1234 020 8564 1235 Tel FAX 07814 100200 Data Mobile gertie@somecompany.co.uk Email Address Book
Contacts Table Field Record
Entities and Attributes • “I want to record the names and addresses of my contacts along with their phone numbers, FAX numbers, mobile numbers and their email addresses” • Entity: Contact • Attributes of Contact:- Name- Address- Phone- FAX- Mobile- Email
Ordered Collection of Data • Characteristics:- each Record is unique- repetition of data is avoided • Database Tables follow certain rules called:- First Normal Form- Second Normal Form- Third Normal Form
Primary Key • A Field (or fields) which has a unique value for each record • Eg. - National Insurance number- Car registration number- Passport number
First Normal Form • Data is atomic, self-contained and independent • No field in a table contains any repeating groups • No record in a table contains repeating groups
Second Normal Form • All the non-key columns must be dependent on the entire primary key
Third Normal Form • All the non-key columns of the table must be only dependent on the primary key and not on each other
Identifying Entities and Relationships • Read the scenario and pick out the nouns • Pick out which nouns are giving information about another noun eg Name is telling you additional information about Contact • In this case Contact is the Entity and Name is an Attribute of that Entity
Exercise: Entities and Attributes • Pick out the database entities and suggest useful attributes: • A library has about 300 borrowers who complete a registration form when they join. There are about 25,000 books available for loan. A loan is for 2 weeks and may be renewed over the phone. Overdue books are charged at 10p per day. If a book is out on loan, another borrower may request it.
Text Data • For example the Name field • The size of the field is determined by the maximum number of characters you want to store • The size of the field is the same for each record so empty spaces waste storage space
Number Data • For Example in the Salary field • The size of the field is determined by the maximum precision of the numbers you want to store • Integer • Long Integer • Single • Double
Date Data • Stored on the computer as a number • Different Formats eg:Short date – 01/02/06
Logical Data • Otherwise known as ‘Yes/No’ • Takes up minimal storage space
Yellow 22 3 Apple A plum 22 3 A plum Apple Yellow Sorting on Text Data Sort Ascending
Points to Consider • What type of data is a telephone number? • How can you name files on a Windows system with digits so that they sort correctly?
Purchase Purchase ID Customer ID Date Item Amount Customer Customer ID Name Address Phone Credit Limit PK PK Linking on the Primary Key
Linked Fields Linked Fields One to Many Relationship
Most Common Types of Relationship Staff Purchase Customer Employee Contractor One to ManyWhere one record in one tablelinks with many in the other table One to OneWhere one record in one tablelinks with one in the other table
Borrower Book Many to Many LinkA borrower can borrow many books and a book may be borrowed by many borrowers – over a period of time Many to Many Relationship
Resolving Many to Many Relationships Many to Many Links are impossible to programme into a database system.This type of relationship may be resolved into 2 many to one relationships. Book Borrower Loan
Scenario • Draw an E-R diagram: • Northwind is a wholesaler of delicatessen products. Customers are all over the world and each member of the sales team may deal with any customer. Products is kept in a central warehouse and items are ordered from Suppliers when numbers reach the re-order level. An Order may consist of several different products and may be delivered by any one of three Shippers.