cs370 spring 2007 cs 370 database systems lecture 4 introduction to database design
TRANSCRIPT
CS370 Spring 2007
CS 370 Database SystemsCS 370 Database Systems
Lecture 4 Introduction to Database
Design
CS370 Spring 2007
Database Design Steps
Requirements Analysis
Conceptual Database Design
Logical Database Design
Physical Database Design
Application & Security Design
Maintenance
Schema Refinement
CS370 Spring 2007
Requirements AnalysisRequirements Analysis
What data is to be stored in the database, what applications must be built on top of it etc.
In other words, find out what users want from the database
Its usually an informal process that involves:- Discussions with user groups A study of the current operating environment And how it is expected to change and so on.
CS370 Spring 2007
Conceptual & Logical Database DesignConceptual & Logical Database Design
Involvement of ER (entity relationship) model. The task of the logical design step is to convert
an ER schema into a relational database schema (will be discuss later).
CS370 Spring 2007
Schema RefinementSchema Refinement
This step involves, analyzing the collection of relations in relational database schema to identify potential problems, and to refine it
Normalization is involved in this step
CS370 Spring 2007
Physical Database DesignPhysical Database Design
This step may simply involve building indexes on some tables and clustering some tables
CS370 Spring 2007
Application & Security DesignApplication & Security Design
Any software project that involves a DBMS must consider aspects of the application that go beyond the database itself
Implementation phase is also merge in this step
CS370 Spring 2007
MaintenanceMaintenance
It involves monitoring, repairing and enhancing the capability of database
Usually done by periodic audits
CS370 Spring 2007
Some Basic ConceptSome Basic Concept
A database can be modeled as a collection of entities relationship among entities
An entity is an object that exists independently and is distinguishable from other objects. an employee, a company, a car, etc.
CS370 Spring 2007
• An entity set is a set of entities of the same type.
E.g., a set of employees, a set of departments also called entity types
EmployeeEntity Type :
e1
e2
e3
Entity set:…
The actual employees
A general specificati
on
CS370 Spring 2007
AttributesAttributes
• Properties of an entity or a relationship– name, address, weight, height are properties of a
Person entity.
CS370 Spring 2007
Types of AttributesTypes of Attributes
– A simple attribute cannot be subdivided
• Examples: Age, Sex, and Marital status
– A composite attribute can be further subdivided to yield additional attributes
• Examples:
– ADDRESS : Street, City, State, Zip
– PHONE NUMBER, Area code, Exchange number
CS370 Spring 2007
• Composite attribute
Country
Employee
Address
Street
City
EmpNo
Name
CS370 Spring 2007
– Single-valued attribute can have only a single value• Examples:
– A person can have only one social security number– A manufactured part can have only one serial number
– Multivalued attributes can have many values• Examples:
– A person may have several email addresses– A household may have several phones with different nu
mbers
Employee
Phone
Types of AttributesTypes of Attributes
CS370 Spring 2007
– A derived attribute is not physically stored within the database; instead, it is derived by using an algorithm.
• Example: AGE can be derived from the data of birth and the current date.
Employee
Age
Bonus
Types of AttributesTypes of Attributes
CS370 Spring 2007
Key AttributesKey Attributes
• A set of attributes that can uniquely identify an entity• A key is a minimal set of attributes whose values uniquely identify an
entity in the set.
EmployeeEmpNo
Name
EmpNo Name . . .123456 Ahmed Khan . . .
456789 . . .
146777 . . .Ali Tahir
Sara Sheikh
ERD
tabular
CS370 Spring 2007
Key AttributesKey Attributes
• Composite key: Name or Address alone cannot uniquely identify an employee, but together they can!
Employee
Name
Address
CS370 Spring 2007
Key AttributesKey Attributes
• An entity may have more than one key– e.g., EmpNo, (Name, Address)– only one is selected as the key. (sometimes called the Pri
mary key)
Employee
EmpNo
Name
Address
In many cases, a key is artificially introduced (e.g., EmpNo) to make applications more efficient.