Database Management Systems

1
Database Management Systems Programming
LIS 558 - Week 5 ER Model Transformation Normaliza
tion

• Faculty of Information Media Studies
• Summer 2000

2
Class Outline

• E-R Transformation
• E-R Transformation Exercises
• Break
• Normalization
• Normalization Exercises

3
Steps to E-R ModelTransformation

• 1. Identify entities
• 2. Identify relationships
• 3. Determine relationship type
• 4. Determine level of participation
• 5. Assign an identifier for each entity
• 6. Draw completed E-R diagram
• 7. Deduce a set of preliminary skeleton tables
  along with a proposed primary key for each table
  (using cases provided)
• 8. Develop a list of all attributes of interest
  (not already listed and systematically assign
  each to a table in such a way to achieve a 3NF
  design (i.e., no repeating groups, no partial
  dependencies, and no transitive dependencies)

4
Transforming an E-R Model

• General Rules Governing Relationships among
  Tables
• 1. All primary keys must be defined as NOT NULL.
• 2. Define all foreign keys to conform to the
  following requirements for binary relationships.
• 1M Relationship
• MN Relationship
• 11 Relationship
• Weak Entity

5
Transforming an E-R Model

• 1M Relationships
• Create the foreign key by putting the primary key
  of the one (parent) in the table of the many
  (dependent).
• Foreign Key Rules

6
Transforming an E-R Model

• Weak Entity
• Put the key of the parent table (strong entity)
  in the weak entity.
• The weak entity relationship conforms to the same
  rules as the 1M relationship, except foreign key
  restrictions
• NOT NULL
• ON DELETE CASCADE
• ON UPDATE CASCADE
• MN Relationship
• Convert the MN relationship to a composite
  (bridge) entity consisting of (at least) the
  parent tables primary keys.

7
Transforming an E-R Model

• 11 Relationships
• If both entities are in mandatory participation
  in the relationship and they do not participate
  in other relationships, it is most likely that
  the two entities should be part of the same
  entity.

8
Transforming an E-R Model

• Case 1 MN, Both sides MANDATORY

9
Transforming an E-R Model

• Case 2 MN, Both sides OPTIONAL

10
Transforming an E-R Model

• Case 3 MN, One side OPTIONAL

11
Transforming an E-R Model

• Cases 1-3 MN

M
N
1
1
PATIENT
DRUG
prescribed
PATIENT (PATIENT_ID, PATIENT_LNAME,
PATIENT_PHYSICIAN,...) DRUG (DRUG_ID, DRUG_NAME,
DRUG_MANUFACTURER, ...) PRESCRIBE(PATIENT_ID,
DRUG_ID, DOSAGE, DATE)
NOTE The relationship may have its own
attributes.
12
Example of decomposing entitieswith a binary MN
relationship

• StudentsClasses have an MN relationship,
  therefore, decompose to three tables.

bridge table
13
Transforming an E-R Model

• Case 4 1M, Both sides MANDATORY

1
M
EMPLOYEE
PRODUCT
checks
EMPLOYEE (EMP_ID, EMP_DEPT, ) PRODUCT (PROD_ID,
PROD_NAME, PROD_FIBRE, EMP_ID... )
14
Transforming an E-R Model

• Case 5 1M, Both sides OPTIONAL

M
1
has
PHYSIOTHERAPIST
CLIENTS
PHYSIOTHERAPIST (PT_ID, PT_LNAME, ...) CLIENT
(CLIENT_ID, CLIENT_LNAME, CLIENT_OHIP, PT_ID)
15
Transforming an E-R Model

• Case 6 1M, Many side OPTIONAL, one side
  MANDATORY

1
M
MACHINE
PARTS
contains
MACHINE (MACH_ID, MACH_NAME, MACH_DEPT, ...) PART
(PART_ID, PART_NAME, PART_CATEGORY, , MACH_ID)
16
Transforming an E-R Model

• Case 7 1M, One side OPTIONAL, many side
  MANDATORY

1
M
BAND
MUSICIAN
accepts
BAND (BAND_ID, BAND_NAME, MUSIC_TYPE...) MUSICIAN
(MUSICIAN_ID, MUSICIAN_INSTRUMENT, BAND_ID)
17
Transforming an E-R Model

• Case 8 11, Both Sides MANDATORY

18
Transforming an E-R Model

• Case 8 11, Both Sides MANDATORY

PLUMBER
BUILDING
1
1
assigned
EMPLOYEE
JOB-DESCRIPTION
1
1
has a
EMPLOYEE (EMP_NUM, EMP_LNAME,, JOB_DESC)
19
Transforming an E-R Model

• Case 9 11, Both Sides OPTIONAL

TRAINER
1
1
has
EXERCISER
EXERCISER (EXERCISER_ID, EXERCISER_LNAME,
TRAINER_ID) TRAINER (TRAINER_ID, TRAINER_LNAME,
...)
20
Transforming an E-R Model

• Case 10 11, One Side OPTIONAL, One Side
  MANDATORY

1
1
EMPLOYEE
AUTO
has
EMPLOYEE (EMP_ID, EMP_LNAME, EMP_PHONE,) AUTO
(LIC_NUM, SERIAL_NUM, MAKE, MODEL,, , EMP_ID)
21
Transforming an E-R Model

• Case 11 Weak Entity (Foreign key located in weak
  entity)

22
Case 11. Decomposing Weak Entities

• When the relationship type of a binary
  relationship is 1M between an entity and its
  weak entity, two tables are required one for
  each entity, with the entity key from each entity
  serving as the primary key for the corresponding
  table.
• Additionally, the entity that has a dependency on
  the existence of another entity has a primary key
  that is partially or totally derived from the
  parent entity of the relationship.
• Weak entities must be deleted when the strong
  entity is deleted.

HOSPITAL (HOSP_ID, HOSP_NAME, HOSP_ADDRESS,
...) UNIT (HOSP_ID, UNIT_NAME, HEAD_NURSE, ...)
23
Transforming an E-R Model

• Case 12 Multivalued Attributes

24
Decomposing an IS-A Relationship
Entity CLIENT contains ClientNumber ClientName Add
ress AmountDue SocialInsuranceNumber TaxIdentifica
tionNumber ContactPerson Phone
CLIENT
1
INDIVIDUAL
CORPORATE
CLIENT
CLIENT
Problem Too many NULL values Solution
Separate into CLIENT entity plus several
subtypes
25
Decomposing an IS-A Relationship

• Create a table for the parent entity and for each
  of the child entities or subtypes
• Move the associated attributes from the parent
  entity into the child table to which they
  correspond
• From the parent entity take the entity key and
  add it as the primary key to the corresponding
  table for each child entity
• In the event a table corresponding to a child
  entity already has a primary key then simply add
  the entity key from the parent entity as an
  attribute of the table corresponding to the child
  entity

CLIENT
CLIENT (CLIENT_ID, AMOUNT_DUE, ) INDIVIDUAL_CLIEN
T (CLIENT_ID, SIN, ) CORPORATE_CLIENT(CLIENT_ID,
GST, )
1
INDIVIDUAL
CORPORATE
CLIENT
CLIENT
26
Transforming Recursive Relationships

• 11 - create a foreign key field (duplicate
  values not allowed) that contains the domain of
  primary key

1M - create a foreign key field (duplicate
values allowed) that contains the domain of
primary key
27
Transforming MN Recursive Relationships

• MN - create a second relation that contains two
  foreign keys one for each side of the
  relationship course requires course.

28
Decomposing Ternary relationships

• When a relationship is three-way (ternary) four
  preliminary tables are required one for each
  entity, with the entity key from each entity
  serving as the primary key for the corresponding
  table, and one for the relationship.
• The table corresponding to the relationship will
  have among its attributes the entity keys from
  each entity
• Similarly, when a relationship is N-way, N1
  preliminary tables are required.

29
Transforming an E-R Diagram

• Converting an E-R Model into a Database Structure
• A painter might paint many paintings. The
  cardinality is (1,N) in the relationship between
  PAINTER and PAINTING.
• Each painting is painted by one (and only one)
  painter.
• A painting might (or might not) be exhibited in a
  gallery i.e., the GALLERY is optional to
  PAINTING.

30
(No Transcript)
31
Transforming an E-R Model

• Transformed schema for ARTIST database
• PAINTER(PRT_NUM, PRT_LASTNAME, PRT_FIRSTNAME,
  PRT_INITIAL, PTR_AREACODE, PRT_PHONE)
• PAINTING(PNTG_NUM, PNTG_TITLE, PNTG_PRICE,
  PTR_NUM, GAL_NUM)
• GALLERY(GAL_NUM, GAL_OWNER, GAL_AREACODE,
  GAL_PHONE, GAL_RATE)

32
A Data Dictionary for the ARTIST Database
33
Library Database Example
AUTHOR
BOOK
N
M
M
1
34
University Example
M
N
M
M
advises
N
1
35
E-R Modeling Transformation Exercise
36
E-R Modeling Transformation Exercise

• Create an E-R model and define its table
  structures for the following requirements.
• - An INVOICE is written by a SALESREP. Each sales
  representative can write many invoices, but each
  invoice is written by a single sales
  representative.
• - The INVOICE is written for a single CUSTOMER.
  However, each customer may have many invoices.
• - An INVOICE may include many detail lines (LINE)
  which describe the products bought by the
  customer.
• - The product information is stored in a PRODUCT
  entity.
• - The product's vendor information is found in a
  VENDOR entity.

37
E-R Modeling Transformation Exercise
38
E-R Modeling Transformation Exercise

• Keep in mind that the preceding E-R diagram
  reflects a set of business rules that may easily
  be modified
• For example, if customers are supplied via a
  commercial customer list, many of the customers
  on that list will not (yet!) have bought
  anything, so INVOICE would be optional to
  CUSTOMER
• We are assuming here that a product can be
  supplied by many vendors and that each vendor can
  supply many products. The PRODUCT may be optional
  to VENDOR if the vendor list includes potential
  vendors from which you have not (yet) ordered
  anything.
• Some products may never sell, so LINE is
  optional to PRODUCT... because an unsold product
  will never appear in an invoice line.
• LINE may be shown as weak to INVOICE, because it
  borrows the invoice number as part of its primary
  key and it is existence-dependent on INVOICE
• The design depends on the exact nature of the
  business rules.

39
E-R Modeling Transformation Exercise
40
E-R Modeling Transformation Exercise

• CUSTOMER (CustomerID, )
• INVOICE (InvoiceID, CustomerID, SalesRepID,)
• LINE (InvoiceID, LineID, ProdID,)
• PRODUCT (ProductID, )
• SALESREP (SalesRepID, )
• VENDOR (VendorID,)
• ORDER (OrderID, ProductID, VendorID,)

41
Further E-R Transformation Exercises
42
ER Modeling I handout - Q1

• DIVISION (DivisionID,ManagerID)
• DEPARTMENT (DeptID,DivisionID)
• EMPLOYEE (EmpID, DeptID)
• PROJECT (ProjectID,)
• EMPLOYEE_PROJECT (EmpID, ProjectID,)

not null
null allowed
43
ER Modeling I - Q2

• INSTRUCTOR (InstructorID, HighestDegree, )
• COURSE (CourseID, ClassTitle, )
• CLASS (ClassID, CourseID, InstructorID, Term)
• TRAINEE (TraineeID, )
• ENROLL (TraineeID, ClassID, Term)

All foreign keys not null.
Optionally, create an EnrollmentID attribute to
use as primary key.
44
ER Modeling I - Q3

• CUSTOMER (CustomerID, )
• INVOICE (InvoiceID, CustomerID, SalesRepID,)
• LINE (InvoiceID, LineID, ProdID,)
• PRODUCT (ProductID, )
• SALESREP (SalesRepID, )
• VENDOR (VendorID,)
• SHIP (ShipID, ProductID, VendorID,)

All foreign keys not null
45
ER Modeling I - Q4

• AGENT (AgentID, LName, Region)
• CLIENT (ClientID, LName,)
• MUSICIAN (MusicianID, AgentID, Name,
  DaysAvailable,)
• EVENT (EventID, ClientID, MusicianID, Date, Time,
  Location)
• INSTRUMENT (InsturmentID, )
• MUSICIAN_INSTRUMENT (MusicianID, InstrumentID,
  YearsExperience)

All foreign keys not null.
46
ER Modeling I - Q5

• CITY (CityID, )
• TEAM (TeamID, CoachID, CityID, )
• PLAYER (PlayerID, TeamID,)
• COACH (CoachID, TeamID,)
• GAME (GameID, HomeTeamID, VisitorTeamID,)

All foreign keys not null.
47
ER Modeling II - Q1

• COMPANY (CompanyID, )
• DEPARTMENT (DepartmentID, CompanyID)
• EMPLOYEE (EmployeeID, DepartmentID, )
• DEPENDENT (EmployeeID, DependentID, )
• EMPLOYEE_HISTORY (EmployeeID, HistoryID, )

All foreign keys are not null
48
ER Modeling II - Q2

• MEMBER (MemberID, )
• WORKOUT (WorkoutID, MemberID, Date)
• EXERCISE (ExerciseID)
• WORKOUT_EXERCISE (WorkoutID, ExerciseID,
  NumberSets, NumberReps,)

49
ER Modeling II - Q3

• EMPLOYEE (EmployeeID, NamePositionID)
• PART_TIME_EMPLOYEE (EmployeeID, HourlyRate)
• FULL_TIME_EMPLOYEE (EmployeeID, Salary,
  OfficeRoom, )
• POSITION (PositionID, Title, Job_Description)

All foreign keys not null.
50
ER Modeling II - Q4

• USER (UserID, Name, Department,)
• PROBLEM (ProblemID, TimeSpent, UserID,
  ResolverID,)
• HARDWARE (ProblemID, Description, Solution)
• SOFTWARE (ProblemID, SoftwareVersion, )
• RESOLVER (ResolverID, Name, Phone, Level, )

All foreign keys not null.
51
ER Modeling II - Q5

• EMPLOYEES (EmployeeID, SupervisorID, )
• SKILLS (SkillID, SkillName, )
• EMPLOYEE_SKILL (EmployeeID, SkillID,
  DateAcquired, Certification,)
• PROJECTS (ProjectID, ProjectName, ManagerID,
  StartDate)
• EMPLOYEE_PROJECT (EmployeeID, ProjectID, Role)
• PROJECT_SKILL (ProjectID, SkillID,
  SkillLevelRequired, NumberStaff,)
• DEPENDENTS (EmployeeID, DependentID,
  DateOfBirth)
• WORK_HISTORY(EmployeeID, HistoryID,)
• BENEFITS (BenefitID, BenefitType, Company,
  Contact,)
• EMPLOYEE_BENEFIT (EmployeeID, BenefitID,)

Optionally, create a ProjectSkill_ID attribute
to use as primary key.
All foreign keys are not null.
52
ER Modeling II - Q6

• ORCHARD (OrchardID, Location, )
• SPECIES (SpeciesID, Name, OrchardID)
• DISEASE (DiseaseID, Symptoms, Treatment,)
• SPECIES_DISEASE (SpeciesDiseaseID, SpeciesID,
  DiseaseID, Date,)
• CUSTOMER (CustomerID, )
• ORDER (OrderID, CustomerID, )
• ORDERDETAILS (OrderID, DetailID, SpeciesID,)

Optionally, use the combination of SpeciesID,
DiseaseID and Date as primary key and remove
SpeciesDiseaseID entirely.
All foreign keys not null.
53
Class Outline

• E-R Transformation
• E-R Transformation Exercises
• Break
• Normalization
• Normalization Exercises

54
Transformation Normalization

• 1. Identify entities
• 2. Identify relationships
• 3. Determine relationship type
• 4. Determine level of participation
• 5. Assign an identifier for each entity
• 6. Draw completed E-R diagram
• 7. Deduce a set of preliminary skeleton tables
  along with a proposed primary key for each table
  (using rules provided)
• 8. Develop a list of all attributes of interest
  (not already listed and systematically assign
  each to a table in such a way to achieve a 3NF
  design (i.e., no repeating groups, no partial
  dependencies, and no transitive dependencies)

55
Database Design Problems

• Database design is the process of separating
  information into multiple tables that are related
  to each other
• Single table designs work only for the simplest
  of situations in which data integrity problems
  are easy to correct
• Anomalies (abnormalities) often arise in single
  table designs as a result of inserting, deleting,
  or updating records
• Some tables are better structured than others
  (i.e., result in fewer anomalies)

56
Database Design Problems

• Numerous anomalies can arise during the design of
  databases
• Redundancy
• Multi-valued problems
• Update anomalies
• Insertion anomalies
• Deletion anomalies

57
The Problem with Nulls
1. Nulls used in mathematical expressions -
unknown quantity leads to unknown total value -
misleading value of all inventory
2. Nulls used in aggregate functions - blanks
exist under category - cannot be counted because
they dont exist!
58
Database Design Problems

• Use of the relational database model removes some
  database anomalies
• Further removal of database anomalies relies on a
  structured technique called normalization
• Presence of some of these anomalies is sometimes
  justified in order to enhance performance
• Database design consists of balancing the art of
  design with the science of design

59
Normalization

• Goal in database design to create well-structured
  tables
• Transform E-R models to tables following the
  rules provided
• Assuring tables are well-structured with minimal
  problems (redundancy, multi-valued attributes,
  update anomalies, insertion anomalies, deletion
  anomalies) is achieved using structured technique
  called normalization

60
Normalization

• Normalization is the structured decomposition of
  one table into two or more tables using a
  procedure designed to determine the most
  appropriate split
• Normalization our method of making sure the E-R
  design was correct in the first place

61
Rules for Normalization

• Basic 1 Rule
• The attribute values in a relational table should
  be functionally dependent (FD) on the primary key
  value.
• In any table, a field A is said to be
  functionally dependent on field B if, regardless
  of any insertions or deletions, the value of B
  determines the value of A (in other words only
  one value of A occurs with a particular value of
  B)

62
Rules for Normalization

• First Normal Form (1NF)
• A table cannot have repeating fields or groups
  (i.e., must remove redundant data)
• Repeating groups are removed by creating another
  table which holds those attributes that repeat.
  This second table is then linked to the original
  table with an identifier (i.e., foreign key)

63
Rules for Normalization

• Second Normal Form (2NF)
• Table is in 1NF
• All nonkey fields in a table must be functionally
  dependent on all of the key (i.e., remove all
  partial dependencies)
• 2NF is primarily concerned with dependencies
  involving a concatenated primary key (nonkey
  fields must be functionally dependent on the
  entire concatenated key not just one attribute of
  the composite key)

64
Rules for Normalization

• Third Normal Form (3NF)
• Table is in 2NF
• A nonkey field cannot be functionally dependent
  on another nonkey field (i.e., remove transitive
  dependencies by placing attributes involved in a
  new relational table)

65
Rules for Normalization

• Fourth Normal Form (4NF)
• Boyce-Codd Normal Form (BCNF)
• Fifth Normal Form (5NF)
• Domain-Key Normal Form (DKNF)
• For most database designs 3NF is sufficient
• 3NF is level for designing in this course

66
First Normal Form

• A table is in first normal form if it meets the
  following criteria The data are stored in a
  two-dimensional table with no two rows identical
  and there are no repeating groups.
• The following table in NOT in first normal form
  because it contains a multi-valued attribute (an
  attribute with more than one value in each row).

67
Handling multi-valued attributes Incorrect
Solutions
68
Handling multi-valued attributes Correct Solution

• Create another entity (table) to handle multiple
  instances of the repeating group. This second
  table is then linked to the original table with
  an identifier (i.e., foreign key). This solution
  has the following advantages
• no limit to the number of hobbies per member
• no waste of disk space
• searching becomes much easier within a column
  (e.g., who likes hiking?)

69
Handling Repeating Groups

• An attribute can have a group of several data
  entries. Repeating groups can be removed by
  creating another table which holds those
  attributes that repeat. This second table
  (validation table) is then linked to the original
  table with an identifier (i.e., foreign key)
• Advantages fewer characters tables reduces
  miskeying, update anomalies

70
Second Normal Form

• A table is in second normal form if it meets the
  following criteria The relation is in first
  normal form, and, all nonkey attributes are
  functionally dependent on the entire primary key
  (no partial dependencies).
• Applies only to tables that have a composite
  primary key.
• In the following table, both the EmpID and
  Training (composite primary key) determine Date,
  whereas, only EmpID (part of the primary key)
  determines Dept.

71
Removing Partial Dependencies

• Remove partial dependencies by separating the
  relation into two relations. Reduces the
  problems of
• update anomalies
• delete anomalies
• insert anomalies
• redundancies

72
Third Normal Form

• A table is in third normal form if it meets the
  following criteria The relation is in second
  normal form, and, a nonkey field is not
  functionally dependent on another nonkey field
  (no transitive dependencies).
• The following table is in second normal form but
  NOT in third normal form because Member_Id (the
  primary key) does not determine every attribute
  (does not determine RegistrationFee).
  RegistrationFee is determined by Sport.

Member ID ? FName, LName, Lesson Lesson ? Cost
73
Removing non-key Transitive Dependencies

• Remove transitive dependencies by placing
  attributes involved in a new relational table.
  Reduces the problems of
• update anomalies
• delete anomalies
• insert anomalies
• redundancies

74
Normalization Example Video Store

• A video rental shop tracks all of their
  information in one table. There are now 20,000
  records in it. Is it possible to achieve a more
  efficient design? (They charge 10/movie/day.)

VIDEO (Cust_name, Cust_address, Cust_phone,
Rental_date, Video_1, Video_2,
Video_3, VideoType_1, VideoType_2, VideoType3,
Return_date, Total_Price, Paid?)
75
Normalization Example Video Store
76
Is the Video store in 1NF?

• No attributes should form repeating groups -
  remove them by creating another table. There are
  repeating groups for videos and customers.

CUSTOMER (Cust_Num, Cust_Name, Cust_address_Cust_p
hone
VIDEO (VideoNum, VideoName, VideoType
RENTAL (Cust_num, VideoNum, Rental_date,
Return_date, TotalPrice, Paid?)
77
Video Store 1NF (contd)

• Have not yet removed all repeating groups - video
  is a multi-valued attribute - move to another
  table.

RENTALDETAILS (RentalNum, VideoNum)
RENTAL (RentalNum, Cust_Num, Rental_date,
Return_Date, TotalPrice, Paid?)
78
The Video Store is now in 1NF
CUSTOMER (Cust_Num, Cust_Name, Cust_address,
Cust_phone
VIDEO (VideoNum, VideoName, VideoType
RENTALDETAILS (RentalNum, VideoNum)
RENTAL (RentalNum, Cust_Num, Rental_date,
Return_Date, TotalPrice, Paid?)
79
Is the Video Store in 2NF?

• The only table that has a composite primary key
  has no other fields, therefore, yes.

CUSTOMER (Cust_Num, Cust_Name, Cust_address,
Cust_phone
VIDEO (VideoNum, VideoName, VideoType
RENTAL (RentalNum, Cust_Num, Rental_date,
Return_Date, TotalPrice, Paid?)
RENTALDETAILS (RentalNum, VideoNum)
80
Is the Video Store in 3NF?

• Does each attribute in each table depend upon the
  primary key?

81
The Video Store is now in 3NF

• Because, in each table every attribute depends on
  the primary key and not on any other key.

CUSTOMER (Cust_Num,
Cust_Name, Cust_address,
Cust_phone)
VIDEO (VideoNum, VideoName, VideoType)
RENTAL (RentalNum, Cust_Num,
Rental_date)
RENTALDETAILS (RentalNum, VideoNum,
ReturnDate, Amt_Paid)
82
Normalization Example ARTIST
83
(No Transcript)
84
Checking Transformed ER Model

• Transformed schema for ARTIST database
• PAINTER(PRT_NUM, PRT_LASTNAME, PRT_FIRSTNAME,
  PRT_INITIAL, PTR_AREACODE, PRT_PHONE)
• PAINTING(PNTG_NUM, PNTG_TITLE, PNTG_PRICE,
  PTR_NUM, GAL_NUM)
• GALLERY(GAL_NUM, GAL_OWNER, GAL_AREACODE,
  GAL_PHONE, GAL_RATE)

?
1NF?
2NF?
?
?
3NF?
85
Checking Transformed RE Model
86
Checking Transformed ER Model

• CUSTOMER (CustomerID, )
• INVOICE (InvoiceID, CustomerID, SalesRepID,)
• LINE (InvoiceID, LineID, ProdID,)
• PRODUCT (ProductID, )
• SALESREP (SalesRepID, )
• VENDOR (VendorID,)
• ORDER (OrderID, ProductID, VendorID,)

?
depends on placement of attributes
1NF?
2NF?
?
3NF?
87
Normalization Exercises
88
Normalization Exercises
To keep track of office furniture, computers,
printers, and so on, the FOUNDIT company uses the
following table structure Attribute
name Sample value ITEM_ID 2311345-678 ITEM_DES
CRIPTION HP DeskJet 660C printer BLDG_ROOM 325
BLDG_CODE DEL BLDG_NAME Dawn's Early
Light BLDG_MANAGER E. R. Rightonit Given this
information, draw the dependency diagram. Make
sure you label the transitive and/or partial
dependencies.
89
Normalization Exercises
90
Normalization Exercises
91
Normalization Exercises
92
Normalization Exercises
93
Conflicting Goals of Design

• Database design must reconcile the following
  requirements
• Design elegance requires that the design must
  adhere to design rules concerning nulls, derived
  attributes, redundancies, relationship types,
  etc.
• Information requirements are dictated by the end
  users
• Operational (transaction) speed requirements are
  also dictated by the end users
• Clearly, an elegant database design that fails to
  address end user information requirements or one
  that forms the basis for an implementation whose
  use progresses at a snail's pace has little
  practical use.

94
Characteristics of Fields

• Each field within a table must have a unique name
  (avoid spaces and special characters).
• Data within a field must be of the same data
  type. The following are common data types
• character (text or string)
• memo (large character field)
• integer (whole numbers for calculations)
• number (values with decimals for calculations)
• currency (formatted number)
• logical or Boolean (true/false 0,-1 yes/no)
• date/ time (use computers internal
  calendar/clock)
• graphic (picture)

95
Guidelines for Ideal Table Design

• Each table should represents a single theme or
  subject or entity or transaction
• Tables should include primary keys that uniquely
  identify each record of each table
• Avoid the use of smart keys that attempt to embed
  meaning into primary keys (keys should be
  meaningless)
• A primary key should be a unique, random or
  sequential collection of alphabetic, numeric or
  alphanumeric characters
• The domain of primary keys should be large enough
  to accommodate the identification of unique rows
  for the entire potential universe of records
• Use the suffix ID in constructing primary keys to
  ensure they are readily identifiable
• Tables should not contain any of the following
  multipart fields, multivalued fields, calculated
  or derived fields or unnecessary duplicate fields
• There should be a minimum amount of redundant data

96
Common Errors in Database Design

• Flat file database
• Too much data
• Compound fields
• Missing keys
• Bad keys
• Missing relationships
• Unnecessary relationships
• Incorrect relationships

• Duplicate field names
• Cryptic field or table names
• Referential integrity
• Database Security
• Missing or incorrect business rules
• Missing or incorrect constraints

Ashenfelter, J. P. (March 26, 1999). Common
Database Mistakes. Found online at
lthttp//webreview.com/wr/pub/1999/03/26/feature/in
dex3.htmlgt (June 5, 2000).
97
The Well-Structured Database

• E-R modeling is top-down method of designing
• Transforming an E-R model does not guarantee the
  best design (e.g., E-R model could be way off)
• Best to transform E-R model and then check the
  design according to the Cases of normalization
• Normalization is bottom-up method of designing a
  database
• Use both approaches to develop a well-structured
  database

98
For Week 6

• Assignment 2 due June 12
• Structured Query Language
• Discuss project assignment
• Read Rob, Chapter 3.1-3.6 and Chapter 6
• Work on Adamski Tutorial 5