Database

1
Database Design and Administration By Sahar
Mosleh
2
Relational Data Model
3

• File Management Systems
• Before the existence of DBMS, the data were
  stored in separate files.
• There was no link from one file to another
• If the structure of the data changed (ex adding
  more fields), programs that were using the file
  had to change
• Problems became more severe when the number of
  the programs using the files increased over time

4

• Relational Data Model
• Definition
• A relational database is a database where all
  data visible to users is organized strictly as
  tables of data values and where all database
  operations work on these tables
• In this model information is stored in a database
  as simple row/column tables of data
• Next slide shows an example of tables in a
  relational database

5
SalesReps

• Empl_Num Name Age Rep_Office Title
  Hire_Date Manager Quota Sales
• 105 Bill Adams 37 13 Sales Rep
  12-FEB-88 104 350000 367911
• 109 Mary Jones 31 11 Sales Rep
  12-OCT-89 106 300000 392725
• 102 Sue Smith 48 21 Sales Rep
  10-DEC-86 108 350000 474050
• 106 Sam Clark 52 11 VP Sales
  14-JUN-88 275000 299912
• 104 Bob Smith 33 12 Sales Mgr
  19-MAY-87 106 200000 142594
• 101 Dan Roberts 45 12 Sales Rep
  20-OCT-86 104 300000 305673
• 110 Tom Synder 41 Sales Rep
  13-JAN-90 101 75985
• 108 Larry Fitch 62 21 Sales
  Mgr 12-OCT-89 106 350000 361865
• 103 Paul Cruz 29 12 Sales
  Rep 01-MAR-87 104 275000 286775
• Nacy Angelli 49 22 Sales Rep
  14-NOV-88 108 300000 186042
• .
• .

Orders
Order_Num Order_Date Cust Rep MFR Product
QTY Amount 112961 17-DEC-89 2117
106 REI 2A44L 7 31500 113012
11-JAN-90 2111 105 ACI 41003
35 3745 112989 03-JAN-90 2101
106 FEA 114 6 1458 113051
10-FEB-90 2118 108 QSA K47
4 1420 112968 12-OCT-89 2102
101 ACI 41004 34 3978 113036
30-JAN-90 2107 110 ACI 4100Z 9
22500 113045 02-FEB-90 2112
108 REI 2A44R 10 45000 112963
17-DEC-89 2103 105 ACI 41004 28
3276 113013 14-JAN-90 2118
108 BIC 41003 1 652 113058
23-FEB-90 2108 109 FEA 112
10 1480 112997 08-JAN-90 2124
107 BIC 41003 1 652 112983
27-DEC-89 2103 105 ACI 41004 6
702 113024 20-JAN-90 2114
108 QSA XK47 20 7100 113062
24-FEB-90 2124 107 FEA 114
10 2430 112979 12-OCT-89 2114
102 ACI 4100Z 6 15000
Products
Mfr_Id Product_Id Description Price
Qty_On_Hand REI 2A45C RATCHET LINK
79 210 ACI 4100Y WIDGET REMOVER
2750 25 QSA XK47 REDUCER
355 38 BIC 41672 PLATE
180 0 IMM 779C 900-LB BRACE
1875 9 ACI 41003 SIZE 3 WIDGET
107 207 ACI 41004 SIZE 4 WIDGET
117 139 BIC 41003 HANDLE
652 3 IMM 887P BRACE PIN 250
24 QSA XK48 REDUCER 134
203 REI 2A44L LEFT HINGE 4500
12 .
Customers
Cust_Num Company Cust_Rep
Credit_Limit 2111 JCP Inc.
103 50000 2102 First Corp.
101 65000 2103 Acme Mfg.
105 50000 2123
Carter and Sons 102 40000
2107 Ace International 110
35000 2115 Smithson Corp. 101
20000 2101 Jones Mfg.
106 65000 2112 Zetacorp
108 50000 2121 QMA Assoc.
103 45000 2114 Orion
Corp. 102 20000
Offices
Office City Region Mgr
Target Sales 22 Denver
Western 108 300000 186042 11 New
York Eastern 106 575000 692637 12
Chicago Eastern 104 800000
735042 13 Atlanta Eastern 105
350000 367911 21 Los Angeles
Western 108 725000 835915
6

• Table
• Definition
• A table is a rectangular object with rows and
  columns
• For example in the office table
• Each row of the office table represents a single
  physical entity
• Each column of the offices table represents one
  item of data that is stored in the database for
  each office
• Ex City column represents the location of the
  office
• An alternative term for column is attribute
• Each row of the table contains exactly one data
  value in each column

7

• In each column of a table, all of the data values
  in that column have the same type. For example
• City column values are words
• Sales values are money type
• Mgr values are integer
• Each column in a table has a column name which is
  written as a heading at the top of the column
• Column names must be unique in a table
• The columns of a table have a left-right order.
  That is defined when the table is first created.
• The order of the column has no effect when any
  action is done against the table

8

• Each table must have at least one column
• Almost all commercial DBMS products impose
  maximum of 255 columns per table
• A table can have zero or more rows
• A table with zero rows is called an empty table
• Order of the rows is not important in a table.
• Most relational DBMSs either do not impose any
  limit on the number of rows or their limit is a
  very large number
• A common limit is approximately 2 billion rows

9

• Primary Key
• Definition
• A primary key is a column or combination of two
  or more columns that uniquely identifies each row
  of a table.
• Since the order of rows in a table is irrelevant,
  rows cannot be identified based on their
  positions in a table
• Ex row 1, row 2, row 20
• In a well-designed relational database each table
  has a primary key.
• If the primary key contains two or more columns,
  it is called a composite primary key

10

• Example of primary key
• Consider the Offices table

Office City Region Mgr
Target Sales 22 Denver
Western 108 300000 186042 11 New
York Eastern 106 575000 692637 12
Chicago Eastern 104 800000
735042 13 Atlanta Eastern
105 350000 367911 21 Los Angeles
Western 108 725000 835915

• Office column (attribute) can be a good choice
  for the primary key because each office has a
  different office id
• However, city is not a good choice because more
  than one office may be located in the same city.

11

• Consider the Products table

Mfr_Id Product_Id Description Price
Qty_On_Hand REI 2A45C RATCHET LINK
79 210 ACI 4100Y WIDGET REMOVER
27.50 25 QSA XK47 REDUCER
355 38 BIC 41672 PLATE
180 0 IMM 779C 900-LB
BRACE 1875 9 ACI 41003 SIZE 3
WIDGET 107 207 ACI 41004 SIZE
4 WIDGET 117 139 BIC 41003
HANDLE 652 3 IMM 887P
BRACE PIN 250 24 QSA XK48
REDUCER 134 203 REI
2A44L LEFT HINGE 4500 12 .

• What is a good primary key for this table?

12

• In this case, MrfId by itself, is not a good
  choice to be a primary key because more than one
  manufacturer may produce more than one product
• Further, ProductId by itself is not a good choice
  either because the same product can be produced
  by more than one manufacturer.
• However, combination of both is unique in every
  row.
• This is an example of composite primary key.
• A table with a primary key is called a relation.
  A relation is a table in which no duplicate rows
  can exist.

13

• Relationship
• How does the parent/child model is represented in
  the relational data model?
• Consider the following two tables

Offices
Office City Regin Mgr
Target Sales 22 Denver
Western 108 300000 186042 11 New
York Eastern 106 575000 692637 12
Chicago Eastern 104 800000
735042 13 Atlanta Eastern
105 350000 367911 21 Los Angeles
Western 108 725000 835915
SalesReps
Empl_Num Name Age Rep_Office Title
Hire_Date Manager Quota Sales 105 Bill
Adams 37 13 Sales Rep 12-FEB-88
104 350000 367911 109 Mary Jones 31
11 Sales Rep 12-OCT-89 106
300000 392725 102 Sue Smith 48 21
Sales Rep 10-DEC-86 108 350000
474050 106 Sam Clark 52 11 VP
Sales 14-JUN-88 275000
299912 . .
14

• The parent is the offices table
• The child is the salesreps table because the
  salesreps works in an office
• Relationships are created by having the same data
  in two or more tables

Offices
Office City Regin Mgr
Target Sales 22 Denver
Western 108 300000 186042 11 New
York Eastern 106 575000 692637 12
Chicago Eastern 104 800000
735042 13 Atlanta Eastern 105
350000 367911 21 Los Angeles
Western 108 725000 835915
SalesReps
Empl_Num Name Age Rep_Office Title
Hire_Date Manager Quota Sales 105 Bill
Adams 37 13 Sales Rep 12-FEB-88
104 350000 367911 109 Mary Jones 31
11 Sales Rep 12-OCT-89 106
300000 392725 102 Sue Smith 48 21
Sales Rep 10-DEC-86 108 350000
474050 106 Sam Clark 52 11 VP
Sales 14-JUN-88 275000
299912 . .
15

• Note that the RepOffice column in salesreps table
  contains the office number of the sales office
  where each sales person works
• The values of the RepOffice column is the set of
  office numbers found in the office column of the
  offices table
• We will see how this restriction is imposed when
  we discuss about creating tables later in the
  course
• For example, it is possible to find the sales
  office where Mary Jones is working by finding
  the value of Mary Jones RepOffice (11) and
  finding the corresponding row offices table
• So, the parent/child relationship between two
  tables A and B is not represented by explicit
  pointers but by common data values stored in the
  two tables
• Programmers must specify this relationship when
  they create the tables

16

• Foreign Key
• Definition
• Foreign key is a column (or combination or two or
  more columns) whose value matches the primary key
  of another table or possibly the same table
• Together, primary key and the foreign key make
  the parent/child relationship in relational data
  models

Primary key
Offices
Office City Regin Mgr
Target Sales 22 Denver
Western 108 300000 186042 11 New
York Eastern 106 575000 692637 12
Chicago Eastern 104 800000
735042 13 Atlanta Eastern 105
350000 367911 21 Los Angeles
Western 108 725000 835915
Foreign key
SalesReps
Emp_Num Name Age Rep_Office Title
Hire_Date Manager Quota Sales 105 Bill
Adams 37 13 Sales Rep 12-FEB-88
104 350000 367911 109 Mary Jones 31
11 Sales Rep 12-OCT-89 106
300000 392725 102 Sue Smith 48 21
Sales Rep 10-DEC-86 108 350000
474050 106 Sam Clark 52 11 VP
Sales 14-JUN-88 275000
299912 . .
17
Example of Foreign Key
Customers
SalesReps
Products
Cust_Num 2111 .. 2102 . 2103
. . .
Empl_Num Name 105 Bill
Adams 109 Mary Jones 102 Sue Smith 106 Sam
Clark .
Mfr_Id Product_Id REI 2A45C .. ACI 4100Y
.. QSA XK47 . ..
Orders
Order_Num Order_Date Cust Rep Mfr
Product QTY Amount 11296 17-DEC-89
2117 106 REI 2A44L 7 31500
113012 11-JAN-90 2111 105 ACI 41003
35 3745 112989 03-JAN-90
2101 106 FEA 114 6 1458
113051 10-FEB-90 2118 108 QSA K47
4 1420 .
18
Entity Relational Diagram (ERD) Modeling
19

• ERD Model
• Entity-Relationship, ER Model
• ER model describes data as entities,
  relationships and attributes
• Entity
• A thing in the real world with an independent
  existence
• Physical existence
• Person, car, house or employee
• Conceptual existence
• Company, job, university course
• Attribute
• Property that describes entity
• employee Name, Address, Age, HomePhone
• companyName, Headquarters, President

20

• Value
• A particular entity will have a value for each of
  its attributes
• The attribute values that describe each entity
  become a major part of the stored data
• Example
• employee Name, Address, Age, HomePhone
• e1 Bill Adams, 561 Machray Hall, 25,
  8831
• e2 John Smith, 563 Queen Hall, 30, 8833

21

• Simple and Derived Attributes
• Simple attribute
• Attributes that are not divisible
• Ex age, SSN, StudentId, etc
• Derived
• Attributes that can be derived from other
  attributes either from the same entity or other
  entities. For example
• Age can be derived from BirthDate or
• GPA can be derived from Grades
• Attribute values can be derived from other
  entities.
• NumberOfEmployees of a department in department
  entity can be derived by counting the number of
  employees who work in that department in the
  employee entity

22

• Null Values
• Not Applicable
• A particular entity does not have an applicable
  value for an attribute
• ApartmentNumber is null because the family lives
  in the house and not in an apartment
• CollegeDegrees is null because the person never
  gone to the college
• Unknown
• Missing (value exists but not known)
• Height of a person
• Not Known (existence is not sure)
• HomePhone, OfficePhone

23

• Entity
• An entity defines a collection object that have
  the same attributes
• Entity is described by its name and attributes
• Example
• Employee Number, Name, Title, Salary
• Project Number, Budget, Location
• Entity instances are instantiations of the entity
• Example
• Employee Joe, Jim, ...
• Project Compiler design, Accounting, ...

24

• ER Notation
• Entity
• Rectangular box
• Enclosing the entity name
• Attribute Name
• Oval
• Attached to its entity by straight line
• Composite Attribute
• Attached to the component attribute by straight
  line

EMPLOYEE
EmpId
EMPLOYEE
25
Attribute Notations
Key Attribute
Derived Attribute
Regular Attribute
26

• Key Attribute
• Values can be used to identify each entity
  uniquely. This plays the role of primary key in a
  table
• The key attribute is underlined in ER diagram
• Composite key Attribute s
• Combination of the two or more attributes must be
  distinct for each instance. This plays the role
  of composite primary key in a table
• Entities may have more than one attribute that
  can be chosen as key attribute. For example,
  StudentId and SSN. However, you only have to
  pick one as the key (primary key) for the entity
• Entities can only have one primary key

27
Entities
PROJECT
SUPPLIER
WAREHOUSE
EMPLOYEE
PART
LOCATION
DEPARTMENT
28
Entities and Attributes
PROJECT
SUPPLIER
Project No
Project Name
Location
PART
WAREHOUSE
EMPLOYEE
Wareh. No
Wareh. Name
Emp. No
Emp. Name
Part No
Part Name
Location
QTY
WGT
Title
Salary
Addr
LOCATION
DEPARTMENT
Dept. No
Dept. Name
City
Manager
29
Entities, Attributes, and Relationship
PROJECT
SUPPLIER
Project No
Project Name
Supplier No
Supplier Name
Budget
Location
use
supply
has
are used by
works on
are supplied by
EMPLOYEE
WAREHOUSE
PART
Emp. No
Emp. Name
Wareh. No
Wareh. Name
Part No
Part Name
Location
QTY
WGT
Title
Salary
Addr
is contained in
Contains
LOCATION
DEPARTMENT
Dept. No
Dept. Name
City
Manager
30
All Notations
Regular entity
Entities
Attributes
Regular
Key
Derived
Relationships
Or
31
Example of ERD
Project Name
Employee Name
Employee No
Project No
Duration
EMPLOYEE
WORKS ON
PROJECT
WORKS ON
Responsibility
Salary
Title
Budget
TotalEmp
Address
Location
32

• Relationships
• A relationship logically associates two or more
  entities with each other
• Binary relationship associates two entities
• Ternary relationship associates three entities
  with each other
• N-ary relationship associates n entities with
  each other

33

• Relationship attributes
• Attributes can be attached to specific
  relationships.
• Attributes that belong to two different entities
  should be placed as the attribute of the
  relationship
• These values provide data about the relationship
  between the participating entity instances.

Responsibility
Duration
Project Name
Employee Name
Project No
Employee No
WORKS ON
PROJECT
EMPLOYEE
WORKS ON
Salary
Title
Budget
34

• Kinds of Relationships (Cardinality)
• Fundamental ones are
• One-to-one
• Many-to-one (one-to-many)
• Many-to-many
• NOTE There can be multiple relationships between
  two entities

WORKS-IN
EMPLOYEE
DEPARTMENT
MANAGES
35

• One-to-One Relationship
• Each instance of an entity class E1 can be
  associated with at most one instance of another
  entity class E2 and vice versa.
• Example
• Each employee can work on at most one project and
  each project employs at most one employee.

Project Name
Duration
Employee Name
Project No
Employee No
1
1
WORKSON
EMPLOYEE
WORKS ON
PROJECT
Salary
Title
Budget
Responsibility
36
One-to-One Relationship
WorksOn Relationship
EMPLOYEE
PROJECT
p1
e1
p2
e2
p3
e3
p4
e4
. . .
. . .
37

• One-to-Many relationship
• Each instance of one entity class E1 can be
  associated with zero or more instances of another
  entity class E2, but each instance of E2 can be
  associated with at most 1 instance of E1.
• Example
• Each employee can work on at most one project
  each project can employ many employees.

Project Name
Duration
Employee Name
Project No
Employee No
1
N
WORKSON
EMPLOYEE
WORKS ON
PROJECT
Salary
Title
Budget
Responsibility
38
One-to-Many Relationship
WorksOn Relationship
EMPLOYEE
PROJECT
p1
e1
p2
e2
p3
e3
p4
e4
. . .
e5
. .
39

• Many-to-Many Relationship
• Each instance of one entity class can be
  associated with many instances of another entity
  class, and vice versa.
• Example
• Each employee can work on many projects each
  project can employ many employees

Project Name
Duration
Employee Name
Project No
Employee No
M
N
WORKSON
EMPLOYEE
WORKS ON
PROJECT
Salary
Title
Budget
Responsibility
40
Many-to-Many Relationship
41
Multiple Relationships
Responsibility
Duration
WORKS-ON
Project Name
Employee Name
Project No
Employee No
N
M
EMPLOYEE
PROJECT
1
1
Salary
Title
Budget
MANAGES
42

• Mapping ERD to Table Step 1
• For each strong entity E in the ERD
• Create a table T that includes all the simple
  attributes of E
• Choose one of the key attributes of E as primary
  key for T
• If the chosen key of E is composite, the set of
  simple attributes that form it will together form
  the primary key of T

Example
Project Name
Project No
Project (ProjectNo, ProjectName, Budget)
PROJECT
WORKS ON
Budget
43

• Step 2 Binary one-to-one Relationship
• For each binary one-to-one relationship
• As you did in step 1, create 2 tables T1 and T2
  for entities E1 and E2 that relate to each other
  by one-to-one relationship.
• Choose one table (say T1) and include the
  primary key of T2 as a foreign key in T1.
• It is better to choose the table with total
  participation not the one with partial
  participation
• Include all the attributes of the relationship as
  attributes of T1
• In Summary Create a foreign key and move
  relationship attributes to any side of the
  one-to-one relationship

44
Example of Binary one-to-one Relationship
Project Name
Duration
Employee Name
Project No
Employee No
1
1
WORKSON
EMPLOYEE
WORKS ON
PROJECT
Salary
Title
Budget
Responsibility
Foreign key that references Project table
Table 1 EMPLOYEE (EmployeeNo, EmployeeName,
Title, Salary, ProjectNo, Duration,
Responsibility, ) Table 2 PROJECT (ProjectNo,
ProjectName, Budget) OR Table 1 PROJECT
(ProjectNo, ProjectName, Budget, EmployeeNo,
Duration, Responsibility) Table 2
EMPLOYEE (EmployeeNo, EmployeeName, Title, Salary)
Foreign key that references Employee table
45
Another example of Binary one-to-one Relationship
Project Name
Start-Date
Employee Name
Project No
Employee No
1
1
MANAGES
EMPLOYEE
WORKS ON
PROJECT
Salary
Title
Budget

• In this case, because PROJECT has total
  participation and EMPLOYEE has partial
  participation, it is a better idea to do the
  following
• Table 1 PROJECT (ProjectNo, ProjectName,
  Budget, EmployeeNo, Start-Date)
• Table 2 EMPLOYEE (EmployeeNo, EmployeeName,
  Title, Salary)

Foreign key that references Employee table
46

• Step 3 Binary One-to-Many Relationship
• For each regular binary one-to-many relationship
  do
• As you did in step 1, create 2 tables T1 and T2
  for entities E1 and E2 that relate to each other
  by one-to-Many relationship.
• Identify the table that represents the
  participating entity at the N-side (say this
  table is T1)
• Include the primary key of T2 as foreign key in
  T1
• Include any simple attributes of the one-to-many
  relationship as attributes of T1
• Summary Create a foreign key and move
  relationship attributes to the N-side of the
  one-to-many relationship

47
Example of Binary One-to-Many Relationship
Project Name
Duration
Employee Name
Project No
Employee No
1
N
WORKS ON
EMPLOYEE
WORKS ON
PROJECT
Salary
Title
Budget
Responsibility
Foreign key that references Project table
Table 1 EMPLOYEE (EmployeeNo,
EmployeeName, Title, Salary,
ProjectNo, Duration, Responsibility) Table
2 PROJECT (ProjectNo, ProjectName, Budget)
48

• Step 4 Binary Many-to-Many Relationship
• For each binary many-to-many relationship
• As you did in step 1, create 2 tables T1 and T2
  for entities E1 and E2 that relate to each other
  by many-to-many relationship.
• Create a new Table T3
• Include as foreign key attributes in T3 the
  primary keys of T1 and T2. Their combination will
  form the primary key of T3
• Include any simple attributes of the many-to-many
  relationship as attributes of T3
• Summary Each many-to-many relationship becomes a
  table with foreign keys to the participants

49
Example of Binary Many-to-Many Relationship
Project Name
Duration
Employee Name
Project No
Employee No
M
N
WORKS ON
EMPLOYEE
WORKS ON
PROJECT
Salary
Title
Budget
Responsibility
Table 1 EMPLOYEE (EmployeeNo, EmployeeName,
Title, Salary) Table 2 PROJECT (ProjectNo,
ProjectName, Budget) Table 3 WORKS-ON
(EmployeeNo, ProjectNo, Duration, Responsibility)
Foreign key that references Project table
Foreign key that references Employee table
50

• Some Comments
• When you create your ERD, it is a better idea to
  follow the following general rules
• You should use the convention that entity type
  and relationship type names are in uppercase
  letters, attribute names are capitalized and role
  names are in lowercase letters
• Another naming consideration involves choosing
  binary relationships names to make the ERD of the
  schema readable from left to right and from top
  to bottom

51

• Example
• Given the following ERD find the corresponding
  tables.

Flight FlightNo, Destination Passenger
PassNo, PassName, PassAddr Fly FlightNo,
PassNo, SeatNo, Meal Baggage BagNo, Weight,
PassNo
52

• Part of the Lab 1 Question
• Given the following ERD, find the corresponding
  tables.