Transcript databases part 1

1
DATABASES
Chapter 9
Asfia Rahman
www.theictroom.com
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
www.theictroom.com
3
Database
Structure method for storing information/data in an organized way.
Tables
A set of similar data/ information about a single entity ( people, places,
objects, events)
Records
A set of information about a single item. Single
occurrence of an entity. One instance of an entity.
Fields
A category/single item of information
within an entity
Field
Value/Data
Asfia Rahman
Value of the field
www.theictroom.com
4
KEYS
Primary Key
Unique identifier
for each record.
Must contain
unique values.
Foreign Key
A field in a table
that refers to a
Primary key in
another table.
Compound Key
Two or more fields
combined to
make a unique
identity
Used to create
the relationships
Asfia Rahman
www.theictroom.com
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
www.theictroom.com
6
FIELD TYPES AND SIZES
• Text
• Alphanumeric
• Numeric (integer/decimal)
• Date/time
• Boolean
Asfia Rahman
Different database management systems
use different names for data types. If you
are using Microsoft Access you will notice
that text is used for alphanumeric data and
number is used for numeric data. Sometimes
the software will also use formatted data as
a data type such as currency. Currency is
actually numeric (usually decimal) and is just
formatted by displaying a currency symbol
with the number.
www.theictroom.com
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
www.theictroom.com
8
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.
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
www.theictroom.com
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
Order
Product
Asfia Rahman
Many to Many
www.theictroom.com
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
www.theictroom.com
11
ERD TASKS
Husband
Club member
Asfia Rahman
Wife
Exclusive member
Book
Author
Booking
Room
Car
Service
Describe each of the
relationships
www.theictroom.com
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
www.theictroom.com
13
CREATING RELATIONSHIP ON
ACCESS
• There should be a field in table B that is the Primary key of Table A
Table A
Asfia Rahman
Table B
www.theictroom.com
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
Asfia Rahman
Sales rep
Employee
Club
member
Exclusive
member
www.theictroom.com
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
www.theictroom.com
16
Step 1: Make sure that
foreign ley filed is indexed
to NO DUPLICATES
Asfia Rahman
Step 2:
Click and
drag the PK
to FK.
Make sure to
CHECK
“referential
integrity
www.theictroom.com
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 A
PK
(one side)
Asfia Rahman
Table B
FK
(many
side)
www.theictroom.com
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
www.theictroom.com
19
Step 1: Make sure that FK
field has same data type
and field size as the PK
Asfia Rahman
www.theictroom.com
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
www.theictroom.com
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
Asfia Rahman
Table B
www.theictroom.com
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
www.theictroom.com
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
www.theictroom.com
24
ONE TO MANY TASK
Asfia Rahman
www.theictroom.com
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
www.theictroom.com
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
www.theictroom.com
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
www.theictroom.com
28
Asfia Rahman
www.theictroom.com
29
Asfia Rahman
www.theictroom.com
30
Asfia Rahman
www.theictroom.com
31
Asfia Rahman
www.theictroom.com
32
Asfia Rahman
www.theictroom.com
33
Asfia Rahman
www.theictroom.com
34
Asfia Rahman
www.theictroom.com
35
Asfia Rahman
www.theictroom.com
36
Asfia Rahman
www.theictroom.com
37
Asfia Rahman
www.theictroom.com
38
Asfia Rahman
www.theictroom.com
39
Asfia Rahman
www.theictroom.com
40
Asfia Rahman
www.theictroom.com
41
Asfia Rahman
www.theictroom.com