![Page 1: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/1.jpg)
©www.teach-ict.com
Relational Databases
![Page 2: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/2.jpg)
©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.
![Page 3: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/3.jpg)
©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
![Page 4: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/4.jpg)
©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.
![Page 5: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/5.jpg)
©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.
![Page 6: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/6.jpg)
©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
![Page 7: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/7.jpg)
©www.teach-ict.com
Entity: Customer
Attributes•First Name
•Surname
•Address
•Telephone Number
•Age
•Gender
![Page 8: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/8.jpg)
©www.teach-ict.com
Entity: VideoAttributes
•Video Title
•Rating
•Film Genre
•Date Released
•Length of film
•Rental price
![Page 9: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/9.jpg)
©www.teach-ict.com
Entity: Video RentalsAttributes
•Date Rented
•Date Returned
![Page 10: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/10.jpg)
©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
![Page 11: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/11.jpg)
©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
![Page 12: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/12.jpg)
©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
![Page 13: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/13.jpg)
©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)
![Page 14: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/14.jpg)
©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
![Page 15: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/15.jpg)
©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)
![Page 16: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/16.jpg)
©www.teach-ict.com
Field Types•Alphanumeric (text)
•Numeric.
•Currency
•Date
•Picture (bitmap)
•Logical/Boolean
•Autonumber
•Memo (for writing notes)
![Page 17: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/17.jpg)
©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.
![Page 18: © Relational Databases. © Entities Data is stored in tables. Each table is concerned with one entity An entity is a](https://reader035.vdocuments.us/reader035/viewer/2022071808/56649ee95503460f94bfafe1/html5/thumbnails/18.jpg)
©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.