Transcript Mid term Exam COVERS chapters 1-3, 7-8 Two parts In class

Mid term Exam
COVERS chapters 1-3, 7-8
Two parts
In class
Take home part (given on Oct 29th, Due Nov5th
in class)
System catalogs
Information about database
• It is automatically created everytime a
database is created
• It contains tables like:
• Systables
• Syscolumns
• Sysindexes
• Sysviews
In ORACLE
User_catalog
• SQL> desc user_catalog;
• Name
Null? Type
• ----------------------------------------- -------- --------------------------• TABLE_NAME
NOT NULL
VARCHAR2(30)
• TABLE_TYPE
VARCHAR2(11)
• Select * from user_catalog;
USER_TABLES
• User_objects
• User_tab_columns
• User_views
You can query using select statements
To get constraints
• SQL> select owner, constraint_type
• from user_constraints
• where table_name ='WROTE'; 2 3
•
•
•
•
•
OWNER
C
------------------------------ AGGARWAL
P
AGGARWAL
R
AGGARWAL
R
• Note P for primary
• R for referential
Systables in ORACLE
http://www.tek-tips.com/viewthread.cfm?qid=1292962&page=3
• there is no such object as systables in oracle. What you
are looking for is
dba_tables - All tables in system, only visible to
administrators
all_tables - All tables in all schema's that the connected
user has access to.
user_tables - all tables in the connected users schema.
Bill
Oracle DBA/Developer
Ch 4 Entity Relationship (ER
Modeling)
Business rules
Business rules are precise statements, derived
from a detailed description of the organization's
operations. When written properly, business
rules define one or more of the following
modeling components:
–
–
–
–
–
–
entities
relationships
attributes
connectivities
cardinalities
constraints
Because the business rules form the basis
of the data modeling process, their
precise statement is crucial to the success
of the database design.
because the business rules are derived from
a precise description of operations,
design's success depends on the accuracy
of the description of operations.
Examples of business rules are:
•
•
•
•
•
•
An invoice contains one or more invoice lines.
Each invoice line is associated with a single invoice.
A store employs many employees.
Each employee is employed by only one store.
A college has many departments.
Each department belongs to a single college. (This
business rule reflects a university that has multiple
colleges such as Business, Liberal Arts, Education,
Engineering, etc.)
• A driver may be assigned to drive many different
vehicles.
• A client may sign many contracts.
• Each contract is signed by only one client.
DATA MODEL
• Representation of complex real-world .
• Goal is to create a DB that is accurate representation of
data needs
• and real world data relationships
• A good DB design begins with a good data model
• ..a method for determining "WHAT" data and
relationships should be
• stored in DB and not "HOW" data will be stored or
processed
• Hardware/software indep.
– ..development is an iterative process
THE E-R Model:
• Translates different data view into a
common framework
• Defines data processing and constraints
requirements
• Peter Chen (1976) was the first person to
introduce the concept of ER modeling.
• An ER model is a detailed logical
representation for an organization
• ER Building Blocks (p 124)
ENTITIES & ATTRIBUTES
Entities:
–
–
–
–
–
–
An object of interest
Person
Place
Event
concept
An entity type consists of entity instances
Attributes
–
–
–
–
–
Properties of an entity
Describes an entity
Attributes have DOMAINS.
Domain is a set of possible values. Two attributes can share the
same domain
Derived Attributes:
• These are calculated or extracted from
other attributes
• Ex: total sales
• Typically a derived field is not stored in
the DB, calculated as needed
Relationship:
Association between entities
•
•
•
•
•
•
1:1
1:m
m:n
Unary...within itself
Binary..with two entities
Ternary..with three entities
Unary (recursive):
Entity has a relationship to itself
• 1:1
• 1:m
• m:n
Super & sub type entities (used extensively in
Object oriented systems)
• One challenge is to RECOGNIZE and
represent entities that are
• almost the same, i.e., the share some
properties but also have
• distinct properties
• Ex: COMPUTER
•
Mainframe
•
Minicomputer
•
personal
• EMPLOYEE
•
Full time
•
Part time
Relationship:
• IS-A super, sub entity relationships are of the type “IS A”;
– Ex: full time employee “IS AN” employee
• HAS-A: between two entities..STUDENT “HAS A” relationship with
CLASS
Inheritance
• All attributes of a super type become
attributes of the subtype
• Each attribute should be located at the
highest level in the hierarchy
CARDINALITY (useful in getting approximate
database size)
(MIN and Max) Number of instances of one entity
that can be associated with each
instance of the other entity
• Place the appropriate number beside the entity
• Minimum
• Maximum
Student <_------_>class
(1,6)
(1,40)
•
student _-------_> major
• patient_------_> pat_history
drives
DRIVER
TRUCK
The Chen ERD Solution for Question 8
1
M
DRIVER
M
1
ASSIGNMENT
TRUCK
The Crow’s Foot ERD Solution for Question 8
DRIVER
gets
ASSIGNMENT
is driven in
TRUCK
Draw the ERD
Database name: Ch03_BeneCo
Table name: EMPLOYEE
Table name: BENEFIT
Table name: JOB
Table name: PLAN
1
M
is assigned to
JOB
EMPLOYEE
1
owns
1
PLAN
M
describes
M
BENEFIT
Example
if you were to develop an ER model for a
video rental store, you would note that
tapes can be rented more than once and
that customers can rent more than one
tape.
A customer can rent many tapes.
A tape can be rented by many customers.
Some customers do not rent tapes.
(Such customers might buy tapes or
other items.) Therefore, TAPE is
optional to CUSTOMER in the
rental relationship.
Some tapes are never rented.
Therefore, CUSTOMER is optional
to TAPE in the rental relationship.
Chen Model
M
CUSTOMER
N
rents
TAPE
Crow’s Foot Model
CUSTOMER
rents
TAPE
Chen Model
1
M
M
CUSTOMER
1
TAPE
RENTAL
(0,N)
(1,1)
(1,1)
(0,N)
Crow’s Foot Model
CUSTOMER
generates
RENTAL
enters
TAPE
Q1/136
Entities
• COMPANY
• DEPARTMENT
• DEPENDENT
• EMPLOYMENT HISTORY
1
M
operates
COMPANY
(1,4)
DEPARTMENT
(1,1)
1
(1,N)
employs
M
1
claims
DEPENDENT
(1,1)
M
(1,1)
EMPLOYEE
(0,N)
1
(0,N)
has
M
(1,1)
EMP_HIST
Q3/p137
DIVISION
operates
DEPARTMENT
employs
runs
EMPLOYEE
is assigned to
PROJECT
manages
1
M
operates
DIVISION
1
DEPARTMENT
(1,1)
(1,N)
1
(1,1)
(1,1)
(1,N)
1
employs
1
runs
(0,1)
M
manages
1
EMPLOYEE
(0,1)
(0,N) 1
(1,1)
M
ASSIGN
M
(1,1)
(1,N)
1
PROJECT
(0,1)
Q7/p139
CUSTOMER
1
generates
SALESREP
1
(0,N)
(0,N)
M
M
writes
(1,1)
1
VENDOR
INVOICE
(1,1)
1
(1,N)
(0,N)
contains
M
(1,1)
delivers
M
1
is written in
INV_LINE
(1,1)
(1,1)
PRODUCT
(0,N)
M
Q2/ p 136 & Q8/p139
Q2/p136
• A COURSE does not necessarily generate a
class during each training period. (Some
courses may be taught every other period or
during some other specified time frames.
• Each CLASS must be related to a COURSE.
• Some instructors may teach a class or do
research
• Not all trainees are likely to be enrolled in
classes during some time period.
• A trainee can take more than one class, and each class
contains many (10 or more) trainees, so there is a M:N
relationship between TRAINEE and CLASS. (Therefore,
a composite entity is used to serve as the bridge
between TRAINEE and CLASS.)
• A class is taught by only one instructor, but an instructor
can teach up to two classes. Therefore, there is a 1:M
relationship between INSTRUCTOR and CLASS.
• Finally, a COURSE may generate more than one
CLASS, while each CLASS is based on one COURSE,
so there is a 1:M relationship between COURSE and
CLASS.
ERD Q2/p170
1
1
INSTRUCTOR
TRAINEE
(0,2)
(0,2)
teaches
M
(1,1)
1
M
CLASS
M
ENROLL
(10,30)
(1,1)
(1,1)
(1,1)
1
generates
COURSE
(0,N)
M
Building an ER model
Q8/P139
Identify entities
DINNER
ENTRÉE
GUEST
Relationships
• Each dinner is based on a single entrée,
but each entrée can be served at many
dinners
ENTREE
DINNER
Cardinality
Cardinality
One entrée can be part of a min of 1 dinner
and a max of many dinners, say n
One dinner can be part of a minimum of one
entrée and a max of one entrée
entree
(1,N)
dinner
(1,1)
Relationship between DINNER and
GUEST
Note there are TWO relationships
A guest can attend many dinners
Vice versa
A guest can have many dinner invitations
Vice versa
invitation
Dinner
Guest
Relationship between DINNER and
GUEST
Note there are TWO relationships
A guest can attend many dinners
Vice versa
Dinner
Attend
Guest
Relationship between DINNER and
GUEST
Second relationship
A guest can have many dinner invitations
Vice versa
Dinner
invitation
Guest
Converting m:n into two 1:m
since Guest and DINNER is m:n we can
break into TWO 1:m
i.e ATTEND entity is intersection of DINNER
& GUEST
DINNER
ATTEND
GUEST
cardinality
What is ATTEND entity ? a combination of a dinner and a guest
• What’s the min number of DINNER in ATTEND? 1
• (a dinner must be part of at lest ONE function (ATTEND)
Max number? N (assuming N is total # of dinners)
An attend can be part as a min part of one dinner and a max of ONE
dinner also
• what’s the min number of ATTEND a GUEST can have?0
• (a guest may not attend any dinner) or they may attend a max of N
dinners
DINNER
(1,N)
ATTEND
(1,1)
(1,1)
GUEST
(0,N)
• Same concepts can be applied to
INVITATION intersection entity
ERD Q 8/p139
1
ENTREE
(1,N)
is part of
M
(1,1)
1
M
DINNER
1
(1,N)
M
1
GUEST
ATTENDANCE
(1,N) (1,1)
(1,1)
M
M
INVITATION
(1,1)
1
(1,1)
(0,N)
(1,N)
EX: Convert following ERD into
relations
assume a student can be advised by one
professor but a professor can advise many
students, a club can have many students
but a student can join only one club, then
the ERD will look as
STUDENT
PROFESSOR
CLUB
Relations will be
STUDENT
(St_ID, st_name,…,Club_ID, Prof_ID)
Note: we must account for both relationships in STUDENT
entity (student, professor) AND (student, club)
CLUB
(Club_ID, location,date_established,..)
PROFESSOR
(Prof_ID, Prof_name, specialty,..)