Transcript Data Modeling

Data Modeling
ISYS 464
Install Oracle 10g Express
• Website to download:
– http://www.oracle.com/technology/products/database/xe/index.html
– Choose Linux or Windows version
– Choose: Oracle Database 10g Express Edition (Western European)
• Installation guide:
– http://download.oracle.com/docs/cd/B25329_01/doc/install.102/b25143/t
oc.htm
• Getting started guide:
– http://download.oracle.com/docs/cd/B25329_01/doc/admin.102/b25610/t
oc.htm
• Two types of accounts:
– System administrator: Remember the name and password used to install
the Oracle.
• This account lets you to create/delete user account.
– User
• Sample database: HR (Human Resource Database)
– This database initially is “locked”. You need to login as an Administrator
to unlock the HR database.
– User name: HR, Password: HR
Start and Login to Oracle 10g Exp
• To start:From Oracle PopUp menu, choose
– Start database: wait for the services to complete:
• The OracleXETNSListener service was started successfully.
• The OracleServiceXE service is starting..............................
• The OracleServiceXE service was started successfully.
– Then choose: Go to database home page
• Login
• Accounts
– System administrator
– User
• Logout
– Stop database
Start and End MySQL
• MySQL is installed as a service.
• To start MySQL:
– Control Panel/Administrative Tools/Services/MySQL/ start
•
•
•
•
MySQL Administrator
MySQL Query Browser
MySQL Command Line Client
To stop MySQL:
– Control Panel/Administrative Tools/Services/MySQL/ Stop
Database Design Process
• Conceptual database design:
– The process of creating a data model independent of
implementation details such as the target database
model and physical considerations.
• Logical database design:
– The process of designing database logical structure
based on a specific database model (such as relational
model), but independent of a particular DBMS and
physical considerations.
• Physical database design:
– The process of implementing the database on a
secondary storage.
Requirements Collection and
Analysis
• The process of collecting and analyzing
information about the organization that is to
be supported by the database system, and
use this information to identify the
requirements for the new system.
NBC Olympic Website
• http://www.nbcolympics.com/
Making Airlines Reservations
• US Airways -http://www.usairways.com/awa/
• Select your seats
• Web check-In
Supermarket Advertisement
LuckyMarket presents another great way to save with your
LuckyMarket Rewards Card! You can get up to 4 FREE
movie tickets!
From Feb. 01 through June 9, 200X, use your LuckyMarket
Rewards Card every time you shop at LuckyMarket. When
you accumulate between $250 - $399.99 in groceries during
the qualifying period, you get 2 FREE movie tickets! And
if you purchase $400 or more during the same period you
get 4 FREE movie tickets!
Fact-Finding Techniques
• Examining documentation
– Defining problem and need for database:
• Internal memos, minutes of meetings, documents that describe
the problem, organizational chart
– Describe the current system:
• Various types of flowcharts and diagrams, data dictionary,
database system design, program documentation
• Interviewing
• Observing the enterprise in operation
• Questionnaires
User Views
• A user view defines what is required of a database
system in terms of the data to be held and
transactions to be performed on the data from the
perspective of a particular job role or enterprise
application area.
• Identifying user views helps to ensure that no
major users of the database are forgotten when
developing the requirements for the new database
system.
• Examples of user views:
– Reports, Forms, documents
Conceptual Database Design
Methodology
• Identify entity types.
• Identity relationship types between the entity
types.
• Identify and associate attributes with entity or
relationship types.
• Determine attribute domains.
• Determine candidate keys and primary key.
• Validate conceptual model:
– Check for redundancy, support required transactions,
review the model with user
Objectives of Database design
• Be able to link related records in the
database.
• Eliminate data duplication.
Entity-Relationship Diagram
• ER modeling is a top-down approach to database
design that begins by identifying the entities and
relationships between entities that must be
represented in the model.
– Relative ease of use.
– Widespread CASE tool support.
– The belief that entities and relationships are natural
modeling concepts in the real world.
– Classifying things according to their various kinds.
ERD Models Entities and Business
Rules
• Example:
– A customer may submit any number of orders.
However, each order must be submitted by
exactly one customer.
– A student may register for a section of a course
only if he or she has successfully completed the
prerequisites for that course.
Entities
• An entity is a person, place, object, event, or
concept in the user environment about which the
organization wishes to maintain data.
–
–
–
–
–
Person: Employee, Student, patient
Place: Warehouse, Store
Object: Product, Machine.
Event: Registration, Sale, Renewal
Concept: Account, Course
• Physical existence:
• Customer, student, product, etc.
• Conceptual existence:
• Bank accounts, sale
Entity Type
• A collection of entities that share common
properties or characteristics.
• An entity type represents a collection of
entities.
• In an ERD, it is given a singular name.
• Diagrammatic representation:
– A rectangle labeled with the name of the entity
Entity Instance
• An entity instance is a single occurrence of
an entity type:
– Student entity: SID, Sname, Major
– Two instances of Student entity type:
• S1, Peter, Bus
• S5, Paul, Sci
Relationship Type
• Relationship: Interaction between entity
types.
– It is an association representing an interaction
among the instances of one or more entity types
that is interest to the organization.
• It has a verb phrase name:
– Faculty teach Course, Faculty advise Student
– Customer open Account, Customer purchase
Product.
Figure 3-10 Relationship types and instances
a) Relationship type
b) Relationship
instances
Binary Relationship
• A relationship involves two entity types.
• Three kinds of Binary Relationship – 1:1
– 1:M
– M:M
• Determined by business rules
M:M Relationship
Boy
Girl
Peter
Mary
Paul
Linda
John
Nancy
Woody
Mia
Alan
Pia
A boy may date 0, 1, or many girls.
A girl may date 0, 1, or many boys.
Note: “Many boys date many girls” is not a correct
interpretation.
1:1 Relationship
Man
Woman
Peter
Mary
Paul
Linda
John
Nancy
Woody
Mia
Alan
Pia
A man may marry 0 or 1 woman.
A woman may marry 0 or 1 man.
1:M Relationship
Father
Child
Peter
Paul
John
Woody
Alan
A father has 1 or many children.
A child has 1 father.
Mary
Brian
Linda
Aron
Nancy
Ronald
Mia
Pia
Cardinality Constraint
• A cardinality constraint specifies the
number of instances of entity type A that
can (or must) be associated with each
instance of entity type B.
• Participation constraint
– Full participation (Mandatory)
– Partial participation (Optional)
Notations
Other Notations
UML Notations:
– 0..1, 1..1
– 0..*, 1..*
– 3..5
Has
Student
1..1
1..1
Account
• Traditional:
Student
1
Has
1
Account
1:1 Relationship
• Examples:
– Husband, Wife
– State, State Governor
– Order, Invoice
1:M Relationship
• Examples:
– Father, Child
– Department, Employee
– Customer, Order
M:M Relationship
• Examples:
– Boy friend, Girl friend
– Bank customer, Bank account
– Student, Student organization
Traditional ERD Notations
1
Student
M
M
Advise
Has
1
Account
M
Enroll
1
Faculty
1
Teach
M
Course
UML ERD Notations
Has
Student
1..1
0..*
Advise
1..1
Account
Enroll
0..*
0..*
1..1
Teach
Faculty
1..1
1..*
Course
Book Notations
Has
Student
Account
Enroll
Advise
Faculty
0..*
Teach
Course
Other Examples
• A database to record visitors and web pages
they view.
• An online shopping website database to
record customers, orders (shopping carts)
and products purchased by customers.
• An auction database to record sellers and
the items they sell, buyers and the items
they purchase.
• Supermarket advertisement
Recursive Relationship
• A relationship type where the same entity
type participates more than once in different
roles.
• Examples:
– Employee – Supervise -- Employee
– Student -- Tutor– Student
– Faculty – Evaluate -- Faculty
Supervise
Supervisor
Employee
Supervisee
Employee
1
M
Supervise
Attributes
• Properties of an entity or a relationship.
• Simple and composite attributes
– Address:Street address, City, State, ZipCode
– Street Address: Number, Street, Apt#
– Phone#: Area Code, number
• Single-valued and multi-valued attributes
–
–
–
–
Student’s Major attribute
Faculty’s DegreeEarned attribute
Vehicle’s Color attribute
Others: PhoneNumber, EmailAddress
• Derived attributes
• Keys: Key attribute uniquely determines an entity.
– Candidate key, primary key, composite key
UML Notations
Student
SID {PK}
Sname
Fname
Lname
Address
Street
City
State
Zip
Phone[1..3]
Sex
DateOfBirth
/Age
SID {PK}
Sname( Fname, Lname)
Address( Street, City, State, Zip)
{Phone}
Sex
DateOfBirth
[Age]
Fname
SID
Lname
Sname
Phone
DateOfBirth
Age
Student
Domains of Attributes
• The set of allowable values for one or more
attributes.
• Input validation
• Examples:
– Sex: F, M
– EmpHourlyWage: Between 6 and 300
– EmpName: 50 characters
Time-Dependent Attributes
• Auditing
• Reconstructing the database state at a point in time:
– Database snapshot
• Regulations:
– Sarbanes-Oxley
• Public Company Accounting Reform and Investor Protection
Act of 2002 and commonly called SOX or SarbOx; July 30,
2002)
• Example:
• ProductPrice: PID, {PriceHistory(Price,
TimeStamp)}
Attributes on Relationship
Online Shopping Cart
CID
Addr
Cname
Customer
1
Has
CartID
M
Date
ShoppingCart
M
Has
M
Product
Price
PID
Pname
Order Form
Online Shopping Cart
CID
Addr
Cname
Customer
1
Has
CartID
M
Date
ShoppingCart
M
Qty
Has
M
Product
Price
PID
Pname
Attributes on Relationship
• Examples:
– Student/Course: Grade
– Order/Product: Quantity
Student
SID
Enroll
0..*
0..*
Course
CID
Grade
Student
M
Enroll
M
Grade
Course
Figure 3-11a A binary relationship with an attribute
Here, the date completed attribute pertains specifically to the
employee’s completion of a course…it is an attribute of the
relationship
Figure 3-11b An associative entity (CERTIFICATE)
Associative entity is like a relationship with an attribute, but it is
also considered to be an entity in its own right.
Note that the many-to-many cardinality between entities in Figure
3-11a has been replaced by two one-to-many relationships with
the associative entity.
N-ary Relationship
• Doctor – Patient – Ailment
• Police – Crimal – Crime
• AirCraft – Bomb – Target
• Note: There is no deterministic relationship
(1:1 or 1:M) between any two of these
entities.
Figure 3-12 Examples of relationships of different degrees (cont.)
c) Ternary relationship
Note1: a relationship can have attributes of its own.
Note2: This ternary relationship exists only if there is
no binary relationship between these three entities.
Problems with ER Models
Connection Traps
• Fan traps: Where a model represents a
relationship between entity types, but the
pathway between certain entity occurrences
is ambiguous
Has
Staff
Oversees
Division
1..*
1..1
1..1
Which branch does Peter work?
Has
Oversees
Branch
Division
1..1
1..*
1..1
Branch
1..*
Staff
1..*
Entity Type not System User or
Organizational Unit
Report
MyCompany
SendTo
Has
Me
Department
Note: An entity type represents a collection of entities.
Figure 3-21 Examples of multiple relationships
a) Employees and departments
Entities can be related to one another in more than one way
Example: Auction site: User and Auction Item