© relational databases. © entities data is stored in tables. each table is concerned with one...
TRANSCRIPT
©www.teach-ict.com
Relational Databases
©www.teach-ict.com
Entities
Data is stored in tables.
Each table is concerned with one entity
An entity is a person/object/event on which data can be held.
In a hospital database the following would all be entities:
Patient, nurse, treatment, ward, appointment, medical test, doctor.
©www.teach-ict.com
Attributes
Each entity has a series of attributes.
These describe different properties of the entity.
The attributes of a patient would include:
Name, address, age, weight, dietary needs etc
©www.teach-ict.com
Relationships
Relationships exist between the different entities.
For example the Doctor will treat many patients but a patient will only see one doctor.
©www.teach-ict.com
Entities, Attributes and Relationships
A Video company wants to set up a database to keep accurate records on which videos have been rented by their customers. From the list on the next slide, decide which items are:
•Entities
•Attributes
•Any relationship between the entities.
©www.teach-ict.com
Task Length of film
First Name
Year released
Video title
E-mail Date rented
Rating Address
Rental price
Age Last Name
Date returned
Phone number
Film Genre
Gender
©www.teach-ict.com
Entity: Customer
Attributes•First Name
•Surname
•Address
•Telephone Number
•Age
•Gender
©www.teach-ict.com
Entity: VideoAttributes
•Video Title
•Rating
•Film Genre
•Date Released
•Length of film
•Rental price
©www.teach-ict.com
Entity: Video RentalsAttributes
•Date Rented
•Date Returned
©www.teach-ict.com
Tables
Customer Table
First Name
Surname
Address
Telephone number
Age
Gender
Video Table
Video title
Rating
Film Genre
Date Released
Length of film
Rental price
Video Rentals Table
Date Rented
Date Returned
©www.teach-ict.com
RelationshipsCustomer Table
Customer ID
First Name
Surname
Address
Telephone number
Age
Gender
Video Table
Video ID
Video title
Rating
Film Genre
Date Released
Length of film
Rental price
Video Rentals Table
Video_Rental ID
Customer ID
Video ID
Date Rented
Date Returned
©www.teach-ict.com
Primary and Foreign KeyCustomer Table
Customer ID
First Name
Surname
Address
Telephone number
Age
Gender
Video Table
Video ID
Video title
Rating
Film Genre
Date Released
Length of film
Rental price
Video Rentals Table
Video_Rental ID
Customer ID
Video ID
Date Rented
Date Returned
©www.teach-ict.com
Primary KeysThis is a field that is used to uniquely define a particular record or line in a table.
Since text fields e.g. surname can be repeated, primary keys are nearly always numeric fields.
They can include:
Membership number, product number, employee number, catalogue number, account number.
If there is no logical number that can be used, then most databases will create one automatically (called auto number)
©www.teach-ict.com
Foreign KeysThis is a field in one table which is also the primary key of another table.
Foreign keys are used to establish a relationship between the main table and other subsidiary tables.
Customer Table
Customer ID
First Name
Surname
Address
Telephone number
Age
Gender
Video Table
Video ID
Video title
Rating
Film Genre
Date Released
Length of film
Rental price
Video Rentals Table
Video_Rental ID
Customer ID
Video ID
Date Rented
Date Returned
©www.teach-ict.com
Standard database notationThe tables we have looked at can be described as follows:
CUSTOMER (CustomerID, Firstname, Surname, Address, TelephoneNumber, Email, Age, Gender)
VIDEO (VideoID, VideoTitle, Rating, FilmGenre, DateReleased, LengthOfFilm, RentalPrice)
VIDEORENTALS (VideoRentalID, CustomerID, VideoID, DateRented, DateReturned)
©www.teach-ict.com
Field Types•Alphanumeric (text)
•Numeric.
•Currency
•Date
•Picture (bitmap)
•Logical/Boolean
•Autonumber
•Memo (for writing notes)
©www.teach-ict.com
Field Types
It is important that when you create tables in Access, that you choose the correct field types right at the beginning.
Similarly, if a field is defined as currency or numeric, text cannot be entered by mistake.
Certain fields are automatically validated when data is entered e.g. dates. If you chose text for a date field, there will be no validation.
©www.teach-ict.com
Field TypesNumeric fields are held differently from text fields. Calculations can only be performed on numeric, date or currency fields.
Field length should be carefully chosen whilst creating the table to ensure that the size of the database is reasonable.