1
Databases
Chapter 9
Asfia Rahman
2 Key terms
Database: a structured method of storing data
Table: a set of similar data (about people, places, objects or
Record: a common word for entity
Entity: a set of data about one thing (person, place, object or event)
Attribute: a category of information within an entity
Field: a common word for attribute
Primary key: a field that contains the unique identifier for a record Database management system: software
used to manage a database
Relationship: the way in which two entities in two different tables are connected
Foreign key: a field in a table that refers to the primary key in another table
Normal form: the extent to which a database has been normalised Index: a list of keys or keywords which
identify a unique record and can be used to search and sort records more quickly
Entity relationship diagram: a diagram that represents the relationships between entities
Flat file: a database stored in a single table
Compound key: two or more fields that form the primary key Referential integrity: data in the foreign key of the
table on the many side of a relationship must exist in the primary key of the table on the one side of a
relationship
Query: a question used to retrieve data from a database
Parameter: data used within the criteria for a query
Asfia Rahman
3
Database Structure method for storing information/data in an organized way.
A set of similar data/ information about a single entity ( people, places,
Tables objects, events)
A set of information about a single item. Single
Records occurrence of an entity. One instance of an entity.
A category/single item of information
Fields within an entity
Field
Value/Data Value of the field
Asfia Rahman
4 Keys
Primary Key Foreign Key Compound Key
Unique identifier A field in a table
Two or more fields
for each record. that refers to a
combined to
Must contain Primary key in
make a unique
unique values. another table.
identity
Used to create
the relationships
Asfia Rahman
5 RDBMS
DBMS
Database management system – Software used to manage a database.
RSBMS
Relational Database management system – Software used to manage a
database that includes relationships.
Asfia Rahman
6 Field types and sizes
Text Different database management systems
Alphanumeric use different names for data types. If you
are using Microsoft Access you will notice
Numeric (integer/decimal)
that text is used for alphanumeric data and
Date/time number is used for numeric data. Sometimes
the software will also use formatted data as
Boolean a data type such as currency. Currency is
actually numeric (usually decimal) and is just
formatted by displaying a currency symbol
with the number.
Asfia Rahman
7 Field sizes
Fields within a table will have field sizes applied to them. This is because most fields are a
fixed length. This means that only a specified amount of data can be stored in each field.
Text and alphanumeric fields will have a length to specify the maximum number of
characters that can be stored. For example, the Product Name in the Product table is limited
to 40 characters. This avoids having lots of wasted storage space where field space is not
used up if the length is too long.
Numbers can also have a field size. This could be defined as the number of digits or it could
be defined as the maximum numeric value.
Dates will always be the same field size as they will always store the date in the same way,
but they can be formatted to be displayed differently.
Some text fields can be formatted to be a variable length which means they can store as
little or as much data as possible. These are sometimes referred to as memo or long text data
types. These are useful for fields that will contain notes or comments.
Asfia Rahman
TIP: Use the Long Integer data type when you create a
foreign key to relate a field to another table's AutoNumber
primary key field.
8 Field sizes in access
Byte — For integers that range from 0 to 255. Storage requirement is a single
byte.
Integer — For integers that range from -32,768 to +32,767. Storage requirement is
two bytes.
Long Integer — For integers that range from -2,147,483,648 to +2,147,483,647.
Storage requirement is four bytes.
Single — For numeric floating point values that range from -3.4 x 1038 to +3.4 x
1038 and up to seven significant digits. Storage requirement is four bytes.
Double — For numeric floating point values that range from -1.797 x 10308 to
+1.797 x 10308 and up to 15 significant digits. Storage requirement is eight bytes.
Decimal — For numeric values that range from -9.999... x 1027 to +9.999... x 1027.
Storage requirement is 12 bytes.
Asfia Rahman
9 ERD
Entity relationship diagram
Shows the relationships between each entity.
Entity is represented by a rectangle.
Relationships are represented by a line
One to One
Sales rep Employee
One to Many
Category Product
Many to Many
Order Product
Asfia Rahman
10 Erd tasks
Draw ERDs to represent the following relationships:
One Airline Seat to one Customer.
One House to many Occupants.
Many Coaches to many Drivers.
Draw an ERD to represent a library model. Within the library, there are
several books. There may be many copies of the same book which are
known as book copies. customers can loan a book copy. a customer can
have many loans but a loan will be for just one customer. Each loan will be
for one book copy. but over a period of time each book copy can be
loaned out many times.
Asfia Rahman
11 Erd tasks
Husband Wife
Describe each of the
relationships
Club member Exclusive member
Book Author
Booking Room
Car Service
Asfia Rahman
12 relationships
Relationships within a database can be used to connect entities together.
A foreign key is an attribute (field) in one entity that connects to a primary
key in another entity. This allows related data to be looked up and found.
Asfia Rahman
13 Creating relationship on access
There should be a field in table B that is the Primary key of Table A
Table A Table B
Asfia Rahman
14 One to one
A one-to-one relationship is when each record in one table only connects
to one record in another table,
Each foreign key value will link to one primary key value and each primary
key value will only be linked to by one foreign
The foreign key can exist on either side of the relationship
Sales rep Employee
Club Exclusive
member member
Asfia Rahman
15 One to one in access
Some rules to follow
At least one of the tables (table A) must have a PK.
The other table (table B) must either have
A PK that is also a FK and will link to the PK in table A.
Or a FK with unique index that will link to PK in table A
Data type and filed size of FK in B and PK in A must match.
Only data items that exist in PK in A can be used in FK in B
How to create:
Both of the common fields (typically the primary key and foreign key fields) must have
a unique index.
This means that the Indexed property for these fields should be set to Yes (No
Duplicates). (very important step!)
If both fields have a unique index, Access creates a one-to-one relationship.
Asfia Rahman
Step 2:
16
Click and
drag the PK
Step 1: Make sure that to FK.
foreign ley filed is indexed Make sure to
to NO DUPLICATES CHECK
“referential
integrity
Asfia Rahman
17 One to many
When each record in one table can connect to many (zero or more)
records in another table
FK will exist within the table on many side of relationship. It will connect to
PK in the one side of relationship.
Table B
Table A
FK
PK
(many
(one side)
side)
Asfia Rahman
18 One to many in access
When creating a one-to-many relationship, there are some rules to follow:
the table on the one side must have a primary key
the table on the many side will have a foreign key
the data type and field size of the foreign key must match the primary key
on the one side
only data items that exist in the primary key on the one side can be used in
the foreign key.
Asfia Rahman
19
Step 1: Make sure that FK
field has same data type
and field size as the PK
Asfia Rahman
20
Step 2: Click on PK on one
side and drag it to FK field
on many side.
Make to select “enforce
referential integrity”
Asfia Rahman
21 many to many
Many-to-many relationships are only conceptual.
They are not used in relational databases because they are converted into
two sets of one-to-many relationships.
In a many-to-many relationship, each record in one table can connect to
many records in another table but each record in the other table can also
connect to many records in the original table.
Table A Table B
Asfia Rahman
22 example
The Order table stores
data about the orders
that are placed including
which products are being
sold. It has a field called
Product IDs which lists the
products being sold on
each order. Each order
can have many products.
Each product can exist on
many orders. There are
many Orders to many
Products.
Asfia Rahman
23 many to many in access
It is necessary to break M-M into 1-M relationship
The rule is to put a LINK table between the two entities.
A new PK is created in the LINK table.
The primary keys for each of original tables are used as FKs in the LINK table
Asfia Rahman
24 One to many task
Asfia Rahman
25 Many to many questions
Resolve the following many-to-many relationships and suggest attribute
names for all three tables:
many Orders to many Products
many Hire Car to many Drivers
many Authors to many Books
many Students to many Classes
many Employees to many Skills
many Doctors to many Patients
Asfia Rahman
26 Referential integrity
Exists when data in the foreign key of the table on the many side of a
relationship exists in the PK of the table on the one side of a relationship
Without referential integrity a relationship cannot be properly set within a
database.
It is a type of lookup validation where the database will check to see if the
related record exists before allowing it to be entered.
If the related record does not exist, then the database will prevent the foreign
key data from being entered.
This is important for maintaining the accuracy of the data within the database.
If details of which classes you attend were entered into a database, but those
classes did not exist, then the database would not be able to give you any
information about the classes
Asfia Rahman
27 tasks
CD 9.10 Sales processing 3.mdb
Open CD 9.10 Sales processing 3.mdb
Open the order table and add Sales Rep IDs 4, 5, 8, 11 and 15 to the records.
Which ones worked?
Which ones did not work?
Why didn't they work?
Try to create a relationship between Product and Category and enforce
referential integrity.
What happens?
Why has this happened?
Correct any data that is causing this problem and try to create the relationship again.
Asfia Rahman
28
Asfia Rahman
29
Asfia Rahman
30
Asfia Rahman
31
Asfia Rahman
32
Asfia Rahman
33
Asfia Rahman
34
Asfia Rahman
35
Asfia Rahman
36
Asfia Rahman
37
Asfia Rahman
38
Asfia Rahman
39
Asfia Rahman
40
Asfia Rahman
41
Asfia Rahman
