Database Structures I

1
Database Structures I

• Shyh-Kang Jeng
• Department of Electrical Engineering/
• Graduate Institute of Communication Engineering
• National Taiwan University

2
Flat Files and Databases

• Flat files
• Databases
• Advantages
• Efficient use of resources
• Access control
• Disadvantages
• Security
• Increasing size and scope

3
File-Oriented Information System
Employee Records
Customer records
Payroll Records
Inventory Records
Sales Records
Payroll Dept.
Customer Service Dept.
Purchasing Dept.
Marketing Dept.
Personnel Dept.
4
Database-Oriented Information System
Customer Service Dept.
Marketing Dept.
Integrated Database
Purchasing Dept.
Payroll Dept.
Personnel Dept.
5
Schema and Subschema

• Schema
• Description of the entire database structure for
  maintenance
• Subschema
• Description of portion of the database pertinent
  to a users needs
• Example University Database
• Student data
• Faculty data

6
Concept Layers
7
Separation of Application Software and DBMS

• Simplifies the design process
• Distributed database
• Provides a means for controlling access
• Data independence
• Allows the application software to be written in
  terms of a simplified, conceptual view (database
  model)

8
Discussions

• Question1
• Question2
• Question3

9
Relational Database Model

• Most popular today
• Simple structure
• Stores data in rectangular tables, called
  relations
• Tuples
• Attributes

10
A Relation Containing Employee Information
Name
SSNum
EmplId
Address
11
A Relation Containing Redundancy
12
Problems with Redundancy

• Lack of efficiency
• e.g., multi-assignment of jobs to an employee
• e.g., job shared by more than one employee
• Data integrity
• e.g., deletes a tuple with the only description
  of a job
• Combines more than one concept into a single
  relation

13
EMPLOYEE Relation
Name
SSNum
EmplId
Address
14
JOB Relation
JobTitle
Dept
JobId
SkillCode
15
ASSIGNMENT Relation
JobId
TermDate
EmplId
StartDate
16
Nonloss Decomposition

• A relation decomposed into smaller relations
  without losing information

17
A Three-Attribute Relation
JobTitle
EmplId
Dept
18
Two Relations
Dept
JobTitle
JobTitle
EmplId
19
Normal Forms (1)

• One fact in one place

1NF
2NF
3NF
BCNF
4NF
5NF
20
Normal Forms (2)

• 1NF
• Table contains only meaningful smallest data unit
• 2NF
• Satisfies 1NF, and has no correspondence between
  non-key data and key values
• 3NF
• Satisfies 2NF, and has no correspondence between
  all non-key data

21
Product Supply Table
22
2NF Product Supply Table
23
2NF Producer Data Table
24
3NF Producer Data Table
25
3NF Area Data Table