Relational Databases:

1
Relational Databases

• 2 - Dimensional tables/relations
• e.g Student
• Relational Operators
• Select - subset of rows from a table
• Project - subset of columns from a table
• Join - Create new table by linking two tables on
  common attributes
• Duplicate Rows not allowed ( Primary key )
• PK Attribute(s)that uniquely define each row
• Cannot have missing (Null) PK value

St
Classfication
Name
2

• Foreign Key
• Defines relationship between entities
• e.g
• S Dept
• Sname DName
• Major Daddress
• Student

Student
Department
Major
S
Name
Dept
3

• Columns whose values point to PK values of
  another table are FKs
• Department
• RULE
• FK values either reference existing PK values or
  they are NULL
• ( Referential Integrity Constraint)

Dept
DAddress
DName
4

• Normalization
• optimizes relational DB design
• Removes potential problems/anomalies
• e.g
• Consider
• Order

Sales Rep
Product
Lists
Stored in
Serves
Places
Warehouse
Customer
Order
5

• Order ( Order, O-Date, (Prod, ProdDesc, Qty))
• First Normal Form
• No repeating groups
• Order ( Order, O-Date, Prod, ProdDesc, Qty)
• Potential Problems
• change a particular products description
• can have inconsistent data
• adding a product when an order does to exist for
  it
• deletions
• Second Normal Form
• in 1NF
• all non-key attributes are dependent on the
  entire PK

Repeating Group
1NF
6

• To convert 1NF to 2NF
• Separate table for each field in PK
• Separate table for all combinations of PK fields,
  taken 2 at a time, 3 at a time etc.
• e.g.
• Order ( Order, Odate)
• Product ( P, Pdesc )
• OrderLine ( Order, P, Qty-Ordered )
• Consider
• Customer ( Cust, Cname, CAddr, SalesRep,
  SRName)
• in 1NF ( no repeating groups)
• in 2NF ( PK is a single field)

7

• Problems
• changing a SalesReps name
• adding a SalesRep who does not yet have a
  customer
• deletions
• Third Normal Form
• in 2NF
• no non-key field is dependent on another non-key
  field
• To change to 3NF
• remove all fields that are dependent on another
  non-key field and place in new table with the
  other field as the key
• e.g.
• SalesRep ( SalesRep, SRName )
• Customer ( Cust, CName, CAddr, SalesRep (FK))

8

• Boyce - Codd NF
• 4NF
• 5 NF

9
Example Course and Instructor Course (
Course , CTitle, Inst(FK) ) Student and
Course EnrollsIn (S-ID, Course, Grade
) Student and Major StudentMajor (S-ID,
Major) Student and CampusOrg BelongsTo (S-ID,
Std-Org) Student(),Instructor (.)
Student
Major
S-ID SName HomeAddr Campus Addr
Major
Campus Org
Course
Std-Org
Course CTitle
Inst InstName Office Phone
Instructor
10
Logical Data Model Physical Data
Model Attribute Data Element, Field Object
Class File, Table, Relation Object
Instance Logical Record Identifier Primary
Key Relationship Foreign Key O-R
Model Database
11

• Modeling relationship between Two entities
• Rule1For 11 (Cardinality) - place PK of one
  rec-type) in to rec-type for another
• Rule2 For 1M or M1 - place PK of rec-type for
  the entity on the 1 side into record-type for
  the entity on the M side
• Rule3For M M- create a new record (Associative
  Entity ) with PK composed of PKs of the two
  entities, and place ant attribute that is
  determined by both PKs into the new rec-type.
• Rule4Discard rec-type with only one data
  element. This has already been included in other
  record-type by above rules

12

• Ternary Relationships
• ( When relationship between at least two of the
  entities is MM)
• Case1 All relationships are MM
• Form new rec-type with PK consisting of PKs of
  all 3 entities. Place any data element determined
  by all 3 PKs into new rec-type.
• Case 2 MM and M1
• Form new rec_type with PK consisting of PKs of
  the MM entities. Place PK of entity on the 1
  side of relationship into this new rec-type as a
  non-key element

13

• Ternary Relationship Example
• Students may have more than one Major and for
  each major, a student is assigned an advisor
• Student ( S-ID,S-Name, S-Addr )
• Advisor ( Ad-ID, Ad Name, Ad Phone)
• Major ( Major )
• Std- Major- Advisor (S-ID, Major, Ad-ID )

S-ID S-Name S-Addr
Student
M
Has
Ad-ID Ad-Name Ad-Phone
1
Advisor
Major
Major
M
14
(No Transcript)
15
(No Transcript)
16
Primary Physician
Patients PatientID P-Address P-DOB P-Hphone P-Wph
one P-Sex P-Occupation
Physician Phy Name PhySpeciality PhyPhone Phy-Pag
er
Appointment App Date App Time Complaint
Hospital HospName
17
Ex Patient Records System PATIENT (PatientID,
P-Name, P-Addr, PDOB,P-HomePhone,
P-WPhone,P-Sex,P-Occupation, PhyName(FK)) PHYSICIA
N (PhyName, PhySpeciality, PhyPhone, PhyPager,
HospitalName(FK)) HOSPITAL (HospitalName) APPOINTM
ENT (PatientID, PhyName, ApptDate, ApptTime,
Complaint) ASSUMPTION Patient does not have
more than one appointment with same physician
on the same day.
18

• DATA MODELLING IN LIFECYCLE PHASES
• System Planning
• Study (Business Mission )
• No data modeling but certain critical data
  entities may be identified
• Definition
• Enterprise Analysis - Enterprise Data Model
  ( Objects)
• Mgmts high level view of critical entities
• Business Area Analysis
• More detail - attributes, relationships
  cardinalities
• (may entail changes to enterprise data model )

19

• Systems Analysis
• Preliminary Investigations
• Application context data model
• Study of current system
• Focus on process modeling
• Requirement Analysis
• Expand and refine data model
• Application essential Data Model ( Implementation
  independent)
• System Design
• Implementation Data Model
• (Files, DBs, normalization,etc)