Database Application Design

1
Database Application Design
January 28, 2000

• Handout 4

2
Course information

• Instructor Dragomir R. Radev (radev_at_si.umich.edu)
  
• Office 305A, West Hall
• Phone [phone]
• Office hours Thursdays 3-4 and Fridays 1-2
• Course page http//www.si.umich.edu/radev/654w00
  
• Class meets on Fridays, 230 - 530 PM, 311 WH

3
The relational model and normalization(contd)
4
Third normal form (3NF)

• HOUSING (SID, Building, Fee)
• Key SID
• FD Building ? Fee, SID ? Building ? Fee
• Transitive dependencies
• Is HOUSING in 1NF, in 2NF?

5
Example
6
Third Normal Form (Contd)

• Definition 2NF with all transitive functional
  dependencies removed
• Example STU-HOUSING (SID, Building) and BLDG-FEE
  (Building, Fee)

7
Boyce-Codd Normal Form (BCNF)

• ADVISER (SID, Major, Fname)
• SID doesnt determine Major
• SID doesnt determine Fname either
• Candidate keys (SID, Major) or (SID, Fname)
• Primary key
• Is ADVISER in 1NF? 2NF?

8
Example
9
BCNF (Contd)

• Are modification anomalies possible in ADVISER?
• BCNF every determinant must be a candidate key
• ADVISER Fname is not a candidate key
• Relations in BCNF have no anomalies as far as
  functional dependencies are concerned

10
Fourth Normal Form (4NF)

• STUDENT (SID, Major, Activity)
• Key (SID, Major, Activity)
• Multivalued dependencies
• SID ? ? Major
• SID ? ? Activity
• Is the relationship between SID and Major a
  functional dependency?

11
Update anomalies
12
4NF (Contd)

• Eliminating multi-valued dependencies
• Definition of 4NF BCNF with no multi-value
  dependencies

13
Domain/Key Normal Form(DK/NF)

• Fagin (1981) showed that a relation in DK/NF is
  one with no modification anomalies and vice versa
• Definition every constraint on a relation is a
  logical consequence of the definition of keys and
  domains

14
Constraints in DK/NF

• Any rule governing static values of attributes
  that is precise enough so that we can determine
  its truth value
• Not included Salesperson salary in the current
  period can never be less than salary in the prior
  period. Why?

15
Keys and domains

• Keys
• Domains physical description and logical
  description
• Definition a relation is in DK/NF if enforcing
  key and domain restrictions causes all of the
  constraints to be met
• No formal algorithm

16
Example 1

• STUDENT (SID, GradeLevel, Building, Fee)
• Key SID
• Constraints Building ? Fee
  SID mustnt start with a 1
• Solution modify domain of SID

17
Example 1 (contd)

• Need to make Building ? Fee a logical consequence
  of a key
• However, building is NOT a key!
• Remove building from STUDENT
• Remove fee from STUDENT
• Every relation should have a single theme!

18
DK definition

• Domain Definitions
• SID in CDDD, where C is in 0,2-9 and D is in
  0-9
• GradeLevel in FR,SO,JR,SR,GR
• Building in CHAR(4)
• Fee in DEC(4)

19
DK definition (Contd)

• Relation and Key Definitions
• STUDENT (SID, GradeLevel, Building)
• Key SID
• BLDG-FEE (Building, Fee)
• Key Building

20
Two more examples

• 2. PROFESSOR (FID, Fname, Class, SID, Sname)
• 3. STU-ADVISER (SID, Sname, FID, Fname,
  GradFacultyStatus)

21
Foundations of relational implementation
22
Defining relational data

• Relations (tables)
• Domains, attributes, constraints
• Relational structure (RS)
• Occurrence (RS data)
• Relational schemas
• Keys design and implementation semantics
• Logical and physical keys

23
Implementing a relational database

• Defining the database structure using a data
  definition language (not always needed)
• Allocating media space
• Creating the database data

24
Relational data manipulation

• Relational algebra
• Transform-oriented languages
• Query-by-example

25
Interfaces to DBMS

• Forms-based data manipulation
• Query/Update language interface
• SELECT Name, AgeFROM PATIENTWHERE Physician
  Levy
• Stored procedure interface
• DO BILLING FOR BDATE 9/15/1999
• Application program interface (API)

26
Msql example

• dbh Msql-gtconnect (msql_hostname,msql_databas
  ename)
• sth dbh-gtquery (select title, id from
  table_name)
• (title,id) sth-gtfetchrow()

27
Relational algebra

• Relational operators
• UNION (for union compatible relations)
• DIFFERENCE STUDENTName-JUNIORName
• INTERSECTION
• PRODUCT (Cartesian product)
• PROJECTION
• SELECTION
• JOIN STUDENT JOIN (SID StudentNumber)
  ENROLLMENT

28
Types of JOIN

• Equijoin
• Natural join
• Inner join
• Outer join (left and right)

29
Expressing queries in relational algebra

• Examples (from book, Chapter 8)

30
Readings for next time

• Kroenke
• Chapter 8 Foundations of Relational
  Implementation
• Chapter 9 Structured Query Language
• YRK (optional)
• Chapter 6 SQL according to MySQL and mSQL