Transcript Relational Modeling

Format for Analysis and Design
Presentation
Format for Analysis and Design Phase
•
Detailed Problem Statement
•
Data Model .. Relational Model
•
ER Diagram .. E-R Model
•
USE Cases describing every success and failure
scenario between system and user
•
Paper Prototype featuring the “screens” used by the
application
•
PowerPoint presentation of Analysis and Design
Relational Model
Codd – 1970
Architecture of
MS Access, SQL-Server, MySql,
Oracle, and most modern databases
Data Models - Levels
• Physical Model – details of how data is stored in files
using a physical schema which describes structure of files
and indices.
• Conceptual Model – hides the details of the physical data
representation and describes syntax of data in terms of
high level concepts using a conceptual schema which
describes data in terms of conceptual units.
– Example : Student(Id : Int, Name: String, Address: String, Status:
String)
• External Model – describes syntax of data in terms of high
level concepts
– (same as conceptual model) but selects concepts appropriate for a
particular user category or view (e.g. Accounting, Payroll, etc.).
• Physical Data Independence – separate schemas at the
physical and conceptual levels.
Levels of data independence
Relational Data Model
Data Model – Set of concepts and language for describing:
• Conceptual and External Schemas. A Schema specifies
the structure of the data stored in the database. There is
one conceptual schema per database but possibly several
external schemas (one per user group). Schemas are
described using a data definition language (DDL).
• Constraints – conditions that data items must satisfy such
as type constraints, key constraints (primary and foreign)
and semantic constraints. Constraints are usually
expressed in the DDL sublanguage.
• Operations – operations on database items to retrieve,
update, insert, or delete, are specified in the data
manipulation language DML.
• Storage – specified by storage definition language (SDL)
The Relational Model
• The Relational Model uses SQL (structured query
language) to describe conceptual and external schemas,
various constraints, operations on database items, and
database storage.
• The Relational Model uses
– An n-ary relation between n attribute sets to model data
– Relation instance is table with
•
•
•
•
column for each attribute set which is atomic
Row or n-tuple for each entity in entity set represented by table
Order of columns does not matter because they are named
Order of rows does not matter because relation is set of rows or
tuples
Relation Schemas and Relational Database
• A relation schema is a relation name (e.g. student, customer, etc.)
followed by a parenthesized list of attribute names with associated
domains, followed by constraints.
– Example: The SQL Create Table statement contains an embedded
relation schema:
– Create Table Student(
– ID
Text(8),
– Name Text(20),
– Address Text(50),
– Status Text(10),
– Primary Key(ID)
– )
• Instances
– An instance of a relation is an actual table and is a legal instance if it
satisfies all constraints.
• A relational database schema is a finite set of relation schemas.
Relation and database instances are actual tables.
Constraints
• Type Constraint : If S is a relation schema and s is an
instance (i.e. a table), then
– 1-1 correspondence between columns of s and attributes
of S
– values in column of s must be in attribute domain.
– Example data types
• Text, memo, date, integer, varchar, currency
• Data atomicity
– Values of an attribute must be atomic – can’t have set
valued attributes such as
• “dependents” as an attribute of employee
• “hobbies” as an attribute of applicant
Constraints (continued)
• Integrity Constraint – statement about all legal
instances of a database. Can be intra or inter –
relational.
– Key constraint : A key of a relation is an attribute which
uniquely identifies a tuple (or row).
• If (StudID, CrsCode, Semester) is a key of the transcript
relation, then any transcript table has at most one row for some
combination of values of these attributes.
– Semantic constraint : Statements concerning the
business rules of an enterprise
• e.g., cannot enter student into a section table if the transcript
table does not contain rows to show he/she has satisfied all the
prerequisites.
Relation Schema
Retail Furniture Store*
Schema for Furniture-Types and
Inventory
Custom Fence
Schema Customers and Fence-Type
Equipment Rental*
Schema for Equipment-Type and Rental
Antique Store*
Schema for Antique
Relation Schema
Retail Furniture Store*
Schema for Furniture-Types and
Inventory
Furniture_Type(Type:String,TypeID:AutoNumber,SupplierID:Integer,Price:Currency)
Inventory(StockID:Autonumber,TypeID:Integer,Color:String,Fabric:String,Quant:Integer)
Custom Fence
Schema Customers and Fence-Type
Customer(Name:String,Address:String,Phone:String,ID:Autonumber,Balance:Currency)
FenceType(Name:String,ID:Autonumber,Price:Currency)
Equipment Rental*
Schema for Equipment-Type and Rental
Equipment-Type(Name:String,ID:Autonumber,Rental:Currency)
Rental(ID:Autonumber,TypeID:Integer,CustomerID:Integer,RentalDate:Date)
Antique Store*
Schema for Antique
Antique(Name:String,ID:autonumber,Description:string,AntiqueDate:date,Price:Currency)
Example of table instances
Schema Transcript(StudID:String,
CrsCode:String,Semester:String,Grade:Grade)
KEY
……
Attribute D in Table T1 is a foreign key that refers to
the candidate key, attribute E, in Table T2.
T1 might be Purchases and D might be CustID
Keys
• A (candidate) key K of a relation schema S is a set of
attributes such that
– Any legal instance s of S does not have two distinct rows which
agree on the values of K
– This condition does not hold for any proper subset of K.
• Two rows can agree on StudID, CrsCode (student repeated course) or
StudID, Semester (student has several courses)
• If K1 contains a proper set K2 which uniquely determines a
row of any legal instance s of S, then K1 is a superkey of S.
• Every relation has a key – the set of all attributes serves as
a superkey since a relation is a set of distinct tuples. If the
set of all attributes is not minimal, let K1 be a proper set
which uniquely identifies all tuples. If K1 is not minimal,
then it contains K2, and so on. This process must terminate
with a minimal set of attributes which is a key.
Keys - continued
•
A schema may have several keys which are referred to as candidate keys.
Example for company with nepotism rule that related people cannot work in
same department:
– Employees( SSNo, Name, Dept, Phone, Address, BirthDate )
•
•
•
•
(SSNo,Name) is a superkey
(Name,Phone) is probably a candidate key
(Name,Address,Dept) is probably a candidate key.
SSNo is a candidate key. If selected by the designer as the key of the
relation, it becomes the primary key.
• Foreign keys - a schemas T is referenced by a schema S by foreign
key F of S if K is a key of T and for every legal instance s and t of S
and T, there is a tuple of t with the same values of K as the values of F
in s (referential integrity)
– Example - Dependents(EmpSSNo, Name, Relation) has a foreign
key EmpSSNo for which there must be a tuple of Employees with same
value of SSNo and EmpSSNo.
Candidate, Primary and Foreign Keys
Used Car Sales
Keys for Cars & Customers
Houseboat Rentals
Keys for Boats & Customers
Rental Property *
Keys for Properties, Units, Tenants
Check Advance *
Keys for Loans, Payments, Customers
Candidate, Primary and Foreign Keys
Used Car Sales
Keys for Cars & Customers
Car – Vehicle Identification Number (VIN)
Customer – CustomerID:autonumber
Houseboat Rentals
Keys for Boats & Customers
Boat – BoatNumber : autonumber
Customer – CustomerID:autonumber
Rental Property *
Keys for Properties, Units, Tenants
Property – PropID : autonumber, Unit - UnitID: autonumber,
Tenant : TenantID : autonumber
Check Advance *
Keys for Loans, Payments, Customers
Loan – LoanID : autonumber, Payment – PaymentID : autonumber
Customer – CustomerID:autonumber
SQL – Data Definition Sublanguage
• Creating relations / tables
– Create Table Employee(
• SSNo char(9), Name char(20), Dept char(5), Phone char(9),
Address char(30), BirthDate char(50)
)
• Primary and Candidate keys
– Create Table Employee(
• SSNo char(9), Name char(20), Dept integer(2), Phone
char(9), Address char(30), BirthDate char(50), Primary Key
(SSNO), Unique (Phone)
)
Data Definition Language DDL
Bicycle
Bicycles
Clinical Record
Prescriptions assuming a Patient table
Machine Shop
Inventory (rods, bars, etc.)
GPS tracking
Trucks
Data Definition Language DDL
Bicycle
Bicycles
Create Table Bicycle( ID : autonumber, MfrID : Integer, Style : String, Price :
currency, Primary Key(ID))
Clinical Record
Prescriptions assuming a Patient table
Create Table Prescriptions( PatientID number, Pdate Date, DrugID number,Primary
Key(PatientID,Pdate,DrugID))
Machine Shop
Inventory (rods, bars, etc.)
Create Table Inventory(MaterialType Text,Cost Currency,Quantity Number, Primary
Key(MaterialType))
GPS tracking
Trucks
Create Table Trucks(TruckVIN text,TDate date,TruckPosition text,Primary Key(TruckVIN))
DDL Create Table (in Access)
System Catalog
• The system catalog is a table in a relational
database called Columns.
• Columns has a schema Columns(AttrName,
RelName, Position, Format) which has four
tuples for the system catalog table and then
a tuple for each attribute of other relations
Catalog relation Columns
(Database table describing itself)
Columns
Courses
Missing Information
• Causes of missing information
– Currently unknown or unavailable. As an example, if a
transcript record is created at the beginning of the semester, Grade
is unavailable until the end of the semester.
– Not applicable. As an example, if maiden name is a field in the
employee table, it is not applicable for male employees.
• The Null Value
– The null value is treated as a member of EVERY domain (number,
char, etc.) but has the property that in doing a retrieval, Select *
From <Table> Where <attribute> = <selected-value>, if the
<selected-value> has been retrieved from the database and is null,
it will not match a null value of <attribute>.
• Restrictions – A primary key should never have a null
value.
SQL – Data Definition Sublanguage continued
• Semantic Constraints – enforce business rules. Suppose a
company rule that says maximum hourly rate is 12.50/hr.
– Create Table Employee(
• SSNo char(9), Name char(20), Dept integer(2), Phone
char(9), Address char(30), BirthDate char(50),
payRate decimal, Primary Key (SSNO), Unique (Phone)
Check ( (5.50 < payRate) and (payRate < 12.51) )
)
Semantic Constraint Examples
• Create Table Employee ( ID Integer, Name Char(20), Salary
Integer, DepID Char(4), Check (0 < (Select Count(*) From
Employee)), Check ((Select Count(*) From Manager) < (Select
Count(*) From Employee) )
– The semantics of the Check clause inside the Create table requires
that every tuple of a relation instance satisfy the conditional
expression inside the Check clause.
– Since the condition in the first Check clause applies to the Employee
relation instance, it is vacuously satisfied, even for the empty
instance.
– The second condition imposes an interrelational constraint requiring
that the number of managers be less than the number of employees.
This is enforced for every tuple of Employee unless Employee is
empty.
User Defined Domains
• SQL permits the definition of a user defined
domain :
– Create Domain Grades Char(1)
• Check ( Value in (‘A’, ‘B’, ‘C’, ‘D’, ‘E’,’F’) )
– Now attribute Grade Grades can be added to the
schema for Student.
Foreign Key Constraints
A Rental Properties table
A Rental Units with foreign
key PID which references
property unit belongs to
Constraint :
For unit u with PID x, there must exist a property p with PropID x.
Foreign Keys & Circularity
• An example of circularity with foreign keys
is to have Employees have a DeptID
attribute referencing Departments and
Departments have a MgrID attribute
referencing Employees. This problem is
avoided by replacing the MgrID in
Departments by third table DeptMgrs with
DeptID and MgrID attributes.
Reactive Constraints
• A reactive constraint or trigger is a static
constraint coupled with a specification of a
reaction to a specified event:
– Semantics : Whenever <event> Do <Action>
– Example : Whenever a Property is deleted, so
are any apartment units which are part of the
property.
This reactive constraint specifies than on the
event of deleting a property, all rental units
belonging to that property will also be deleted.
Cascade Delete
Deleting a rentalproperty in the database results in
this message being displayed: