Document 7132757

Transcript Document 7132757

Database Management Systems

Session 5 1

Objectives

1. Understand the structure of a data table 2. Understand data normalisation 3. Understand and use the terms: - entity - attribute - field - record - primary key 4. Understand the principle of linking tables in a relational database 2

Topics

1. Database Tables 2. Normalisation 3. Linking Tables 4. Database Schema 3

Name Address

Address Book

Record Gertrude Jones Tel 34 Great West Road London W23 8RT 020 8564 1234 FAX 020 8564 1235 Field 07814 100200 Mobile Data [email protected]

Email 4

Name Address

Contacts Table

Field

Phone FAX Mobile Email

Darren Adamson 6 Spring Gardens Gertie Jones 020 7885 1236 34 Great West Road, London W23 8RT 020 8354 1234 08562 100099 07954 100200 dadam@ahomesp.

co.uk

Record gertie@somecomp any.co.uk

Cedric Jones Jessie Wallace 34 Great West Road, London W23 8RT 18 Loch Arber Crescent, Edinburgh EH8 9OP 020 8354 1234 07951 111233 [email protected]

g 0123 266 5889 0123 889 7756 08962 333568 wallacej@servicepr ov.net

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 6

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 7

Primary Key

• A Field (or fields) which has a unique value for each record • Eg. - National Insurance number - Car registration number - Passport number 8

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 9

Second Normal Form

• All the non-key columns must be dependent on the entire primary key 10

Third Normal Form

• All the non-key columns of the table must be only dependent on the primary key and not on each other 11

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 12

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 14

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 15

Date Data

• Stored on the computer as a number • Different Formats eg: Short date – 01/02/06 16

Logical Data

• Otherwise known as ‘Yes/No’ • Takes up minimal storage space 17

• Yellow • 22 • 3 • Apple • A plum

Sorting on Text Data

Sort Ascending • 22 • 3 • A plum • Apple • Yellow 18

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?

Linking on the Primary Key

Customer PK Customer ID Name Address Phone Credit Limit Purchase PK Purchase ID Customer ID Date Item Amount 20

Linked Fields

Customers Customer ID Name

1 Felipe Martins 2 Tom Smith 3 Kimberley Brown 4 Steve Hammer 5 Alfred Futterkist One to Many Relationship

Purchases Purchase ID

1789 1790 1791 1792 1793 1794 1795 1796 1797 1798

Customer ID

3 2 3 3 5 4 1 3 1 5

Item

Fig Rolls Arabiata Sauce Black Olives Porcini Mushrooms Smoked Salmon Cod Roe Cured Ham Sugar Snap Peas Ginger Beer Ground Cumin 21

Most Common Types of Relationship Customer Purchase One to Many Where one record in one table links with many in the other table Staff Employee Contractor One to One Where one record in one table links with one in the other table 22

Many to Many Relationship

Borrower Book Many to Many Link A borrower can borrow many books and a book may be borrowed by many borrowers – over a period of time 23

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 24

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.