Database Management Systems - PowerPoint PPT Presentation

About This Presentation
Title:

Database Management Systems

Description:

Title: The UWO Library System Author: Reference Services Last modified by: Ian B. Whyte Created Date: 10/8/1998 1:26:50 AM Document presentation format – PowerPoint PPT presentation

Number of Views:724
Avg rating:3.0/5.0
Slides: 88
Provided by: Reference3
Category:

less

Transcript and Presenter's Notes

Title: Database Management Systems


1
Database Management Systems Programming
LIS 558 - Week 4 Entity Relationship Modeling II
  • Faculty of Information Media Studies
  • Summer 2000

2
Class Outline
  • Entity-Relationship Modeling Continued
  • E-R Modeling Exercises
  • E-R Model Transformation
  • BREAK
  • E-R Model Transformation Exercises
  • Common database data types
  • Guidelines for designing tables
  • Common database design flaws

3
Weak Entities
  • An entity that is existence dependent on another
    entity (mandatory participation) and
  • Has a primary key that is partially or totally
    derived from the parent entity of the
    relationship
  • In ERD depict weak entity using double lines

contains
COURSE
SECTION
CourseID, ...
CourseID, SectionID, ...
DEPENDENT
EMPLOYEE
has a
EmployeeID, ...
EmployeeID, DependentID, ...
4
Illustration of the Weak Relationship
Between DEPENDENT and EMPLOYEE
5
IS-A Supertypes-Subtypes
  • A subtype entity is an entity that contains a set
    of optional attributes of the supertype entity
    and inherits its attributes and its
    relationships from the supertype entity
  • If the supertype entity is related to mutually
    exclusive subtype entities (can belong to only
    one subtype), indicate with 1 if subtypes are
    overlapping (can belong to more than one), use m

supertype
CONTRACT
CLIENT
1
m
subtype
INDIVIDUAL
CORPORATE
PRODUCTS
SERVICES
The same identifier (e.g., ClientID) is used for
the supertype as well as subtype.
6
Steps to E-R Modeling
  • 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)

7
Developing an E-R Diagram
Entities for the Tiny College Database
  • COURSE
  • CLASS
  • ENROLL (Bridge between STUDENT and CLASS)
  • STUDENT
  • SCHOOL
  • DEPARMENT
  • EMPLOYEE
  • PROFESSOR

8
Developing an E-R Diagram
  • Tiny College Database
  • Each school is composed of several departments.
  • The smallest number of departments operated by a
    school is one, and the largest number of
    departments is indeterminate (N).
  • Each department belongs to only a single school.

9
Developing an E-R Diagram
  • Tiny College Database
  • Each department offers several courses.

10
Developing an E-R Diagram
  • Tiny College Database
  • A department may offer several sections (classes)
    of the same course.
  • A 1M relationship exists between COURSE and
    CLASS.
  • CLASS is optional to COURSE

11
Developing an E-R Diagram
  • Tiny College Database
  • Each department has many professors assigned to
    it.
  • One of those professors chairs the department.
    Only one of the professors can chair the
    department.
  • DEPARTMENT is optional to PROFESSOR in the
    chairs relationship.

12
Developing an E-R Diagram
  • Tiny College Database
  • Each professor may teach up to four classes, each
    one a section of a course.
  • A professor may also be on a research contract
    and teach no classes.

13
Developing an E-R Diagram
  • Tiny College Database
  • A student may enroll in several classes, but
    (s)he takes each class only once during any given
    enrollment period.
  • Each student may enroll in up to six classes and
    each class may have up to 35 students in it.
  • STUDENT is optional to CLASS.

14
Developing an E-R Diagram
  • Tiny College Database
  • Each department has several students whose major
    is offered by that department.
  • Each student has only a single major and
    associated with a single department.

15
Developing an E-R Diagram
  • Tiny College Database
  • Each student has an advisor in his or her
    department each advisor counsels several
    students.
  • An advisor is also a professor, but not all
    professors advise students.

16
Components of the E-R Model
17
(No Transcript)
18
Exercise Problem Rob 2.1-7
19
Exercise Problem - Rob 2.1-7
  • An EMPLOYEE has only one JOB_CODE, but a JOB_CODE
    can be held by many EMPLOYEEs.
  • An EMPLOYEE can have many BENEFITs, and any
    BENEFIT can be assigned to many EMPLOYEEs.

20
Exercise Problem - Rob 2.1-7
21
E-R Modeling I Exercises (distributed last
week) Complete solutions on course website
22
E-R Modeling I - 1
  • 1. Use the following business rules to write all
    appropriate connectivities and cardinalities in
    an E-R diagram
  • A department employs many employees, but each
  • employee is employed by one department,
    although some
  • employees, known as "rovers", are not assigned
    to any
  • department
  • A division operates many departments, but each
  • department is operated by one division
  • An employee may be assigned to many projects and
    a
  • project may have many employees assigned to
    it
  • A project must have at least one employee
    assigned to it
  • One of the employees manages each department
  • One of the employees runs each division.

23
Solution to Q1
24
E-R Modeling I - 2
2. The Hudson Engineering Group (HEG) has
contacted you to create a conceptual model whose
application will meet the expected database
requirements for its training program. The HEG
administrator gives you the following description
of the training group's operating
environment The HEG has twelve instructors and
can handle up to thirty trainees per class. HEG
offers five "advanced technology" courses, each
of which may generate several classes. If a class
has fewer than ten trainees in it, it will be
canceled. It is, therefore, possible for a course
not to generate any classes during a session.
Each class is taught by one instructor. Each
instructor may teach up to two classes or may be
assigned to do research. Each trainee may take up
to two classes per session. Given this
information, do the following - Describe the
relationships between the entities in terms of
connectivity, cardinality, and existence
dependence. - Draw the E-R diagram for HEG.
25
Solution to Q2
26
E-R Modeling II Exercises (distributed last
week) Complete solutions on course website
27
E-R Modeling II - 1
  • 1. Given the following business rules, create the
    E-R diagram, incorporating each of the specified
    relationships
  • - A company operates four departments
  • - Each department in part (a) employs employees
  • - Each of the employees in part b may or may not
    have one or more
  • dependents
  • - Each employee in part (c) may or may not have
    an employment
  • history.

28
Solution to Q1
29
E-R Modeling II - 4
4. A large hospital has just hired you to design
a technical help desk database application. The
hospital has just standardized their complement
of network PCs by acquiring 500 Pentium III 400
MHz workstations. Each workstation has an
appropriate range of applications software
already loaded. The purpose of the helpdesk
database is to track the problems that arise
(hardware and software), the users who report
them, and the person assigned to resolve the
problem. The development of this helpdesk and the
associated database was proposed to reduce
response time, reduce paperwork, centralize
problem resolution, and ensure appropriate
follow-up. Provide a conceptual design for this
database application by creating an
entity-relationship diagram.
30
Solution to Q4
UserID,...
USER
(0,N)
1
ProbID...
reports
HARDWARE
M
(1,1)
1
PROBLEM
ProbID,
is a
(0,1)
(1,1)
M
assigned to
SOFTWARE
(0,N)
1
RESOLVER
ProbID,...
ResolverID,...
31
E-R Modeling II - 5
5. Design a database that tracks employees
skills. Additionally, track various projects and
the skills that are required to perform each
project. Finally, the project must be
appropriately assigned. Include also the
employees supervisor, dependents, work history
and employee benefits.
32
Solution to Q5
supervises
employee
1
1
parent of
M
dependents
1
M
has
M
M
M
work history
acquires
assigned
M
given
N
N
N
project
skill
N
M
benefits
requires
33
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)

34
Developing 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.

35
(No Transcript)
36
Developing an E-R Diagram
  • Summary of Table Structures and Special
    Requirements for the ARTIST database
  • PAINTER(PRT_NUM, PRT_LASTNAME, PRT_FIRSTNAME,
    PRT_INITIAL, PTR_AREACODE, PRT_PHONE)
  • GALLERY(GAL_NUM, GAL_OWNER, GAL_AREACODE,
    GAL_PHONE, GAL_RATE)
  • PAINTING(PNTG_NUM, PNTG_TITLE, PNTG_PRICE,
    PTR_NUM, GAL_NUM)

37
A Data Dictionary for the ARTIST Database
38
Developing an E-R Diagram
  • 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
  • Weak Entity
  • MN Relationship
  • 11 Relationship

39
Developing an E-R Diagram
  • 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

40
Developing an E-R Diagram
  • 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.

41
Developing an E-R Diagram
  • 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.

42
Developing an E-R Diagram
  • Case 1 MN, Both Sides MANDATORY

43
Developing an E-R Diagram
  • Case 2 MN, Both Sides OPTIONAL

44
Developing an E-R Diagram
  • Case 3 MN, One Side OPTIONAL

45
Developing an E-R Diagram
  • Cases 1-3 MN

PATIENT
DRUG
prescribed
M
N
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.
46
Example of decomposing entitieswith a binary MN
relationship
  • StudentsClasses have an MN relationship,
    therefore, decompose to three tables.

bridge table
47
Developing an E-R Diagram
  • 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... )
48
Developing an E-R Diagram
  • 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)
49
Developing an E-R Diagram
  • 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)
50
Developing an E-R Diagram
  • Case 7 1M, One Side OPTIONAL, One Side MANDATORY

1
M
BAND
MUSICIAN
accepts
BAND (BAND_ID, BAND_NAME, MUSIC_TYPE...) MUSICIAN
(MUSICIAN_ID, MUSICIAN_INSTRUMENT, BAND_ID)
51
Developing an E-R Diagram
  • Case 8 11, Both Sides MANDATORY

52
Developing an E-R Diagram
  • 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)
53
Developing an E-R Diagram
  • Case 9 11, Both Sides OPTIONAL

TRAINER
1
1
has
EXERCISER
EXERCISER (EXERCISER_ID, EXERCISER_LNAME,
TRAINER_ID) TRAINER (TRAINER_ID, TRAINER_LNAME,
...)
54
Developing an E-R Diagram
  • 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)
55
Developing an E-R Diagram
  • Case 11 Weak Entity (Foreign key located in weak
    entity)

56
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.

1
M
contains
UNIT
HOSPITAL (HOSP_ID, HOSP_NAME, HOSP_ADDRESS,
...) UNIT (HOSP_ID, UNIT_NAME, HEAD_NURSE, ...)
57
Developing an E-R Diagram
  • Case 12 Multivalued Attributes

58
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
59
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
60
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
61
Transforming MN Recursive Relationships
  • MN - create a second relation that contains two
    foreign keys one for each side of the
    relationship

62
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.

63
Library Database Example
AUTHOR
BOOK
N
M
M
1
64
University Example
M
N
M
M
advises
N
1
65
E-R Modeling Transformation Exercise
66
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.

67
E-R Modeling Transformation Exercise
68
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.

69
E-R Modeling Transformation Exercise
70
E-R Modeling Transformation Exercise
  • CUSTOMER (CustomerID, )
  • INVOICE (InvoiceID, CustomerID, SalesRepID,)
  • LINE (InvoiceID, LineID, ProdID,)
  • PRODUCT (ProductID, )
  • SALESREP (SalesRepID, )
  • VENDOR (VendorID,)
  • SHIP (ShipID, ProductID, VendorID,)

71
Further E-R Transformation Exercises
72
ER Modeling I handout - Q1
  • DIVISION (DivisionID,ManagerID)
  • DEPARTMENT (DeptID,DivisionID)
  • EMPLOYEE (EmpID, DeptID)
  • PROJECT (ProjectID,)
  • EMPLOYEE_PROJECT (EmpID, ProjectID,)

not null
null allowed
73
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.
74
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
75
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.
76
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.
77
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
78
ER Modeling II - Q2
  • MEMBER (MemberID, )
  • WORKOUT (WorkoutID, MemberID, Date)
  • EXERCISE (ExerciseID)
  • WORKOUT_EXERCISE (WorkoutID, ExerciseID,
    NumberSets, NumberReps,)

79
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.
80
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.
81
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.
82
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.
83
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)

84
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

85
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

John Paul Ashenfelter, Common Database
Mistakes, May 26, 1999, lthttp//webreview.com/wr/
pub/1999/03/26/feature/index3.htmlgt (Oct 10,
1999).
86
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

87
The Challenge of Database Design Conflicting
Goals
  • Conflicting Goals
  • Design standards (design elegance)
  • Processing speed
  • Information requirements
  • Design Considerations
  • Logical requirements and design conventions
  • End user requirements e.g., performance,
    security, shared access, data integrity
  • Processing requirements
  • Operational requirements
  • Documentation
Write a Comment
User Comments (0)
About PowerShow.com