information resources management february 13, 2001

Information Resources Information Resources ManagementManagement

February 13, 2001February 13, 2001

AgendaAgenda

AdministriviaAdministrivia Ternary Cardinality (revisited)Ternary Cardinality (revisited) The Relational ModelThe Relational Model Converting an E-R Model to a DBConverting an E-R Model to a DB Exam ReviewExam Review

AdministriviaAdministrivia

Homework #3Homework #3 Exam #1, next week 2/20Exam #1, next week 2/20

Ternary Relationships - Ternary Relationships - CardinalityCardinality For the entity in question,For the entity in question,

For each For each unique pairunique pair of the other of the other entities,entities,

How many of the entity can there be? How many of the entity can there be? One One More than one (many)More than one (many)

Ternary CardinalityTernary Cardinality

A B

C

Has

For each unique B/C pair, how many A’s?

Ternary CardinalityTernary Cardinality

A B

C

Has

For each unique B/C pair, how many A’s?

ExampleExample(one-to-one-to-one):(one-to-one-to-one):

Employee is assigned a phone number Employee is assigned a phone number for a project. A phone number is used for a project. A phone number is used only for that employee and project.only for that employee and project.

Example (Example (one-to-one-to-one):one-to-one-to-one):

Employee

Project

PhoneNumber

Has

Employee is assigned a phone number for a project. A phone number is used only for that employee and project.

For each project/phone# pair, how many employees?

Example (Example (one-to-one-to-one):one-to-one-to-one):

Employee

Project

PhoneNumber

Has

Employee is assigned a phone number for a project. A phone number is used only for that employee and project.

For each project/employee pair, how many phone numbers?

Example (Example (one-to-one-to-one):one-to-one-to-one):

Employee

Project

PhoneNumber

Has

Employee is assigned a phone number for a project. A phone number is used only for that employee and project.

For each employee/phone# pair, how many projects?

Example (Example (one-to-one-to-one):one-to-one-to-one):

Employee

Project

PhoneNumber

Has

Employee is assigned a phone number for a project. A phone number is used only for that employee and project.

ExampleExample(one-to-one-to-many):(one-to-one-to-many):

Employee assigned to a project works Employee assigned to a project works at one location for that project but can at one location for that project but can work at different locations for different work at different locations for different projects. At a location an employee projects. At a location an employee only works on one project, but there only works on one project, but there can be many employees working on can be many employees working on that same project.that same project.

ExampleExample(one-to-one-to-many):(one-to-one-to-many):

Employee

Project

Location

Has

ExampleExample(one-to-many-to-many):(one-to-many-to-many):

Employee on a project has one Employee on a project has one manager. Manager can manage manager. Manager can manage several projects. Each project has several projects. Each project has one manager. Manager can manage one manager. Manager can manage the same employee on different the same employee on different projects.projects.

ExampleExample(one-to-many-to-many):(one-to-many-to-many):

Employee

Project

Manager

Has

ExampleExample(many-to-many-to-many):(many-to-many-to-many):

Employees use many skills on many Employees use many skills on many projects and each project has many projects and each project has many employees with varying skills.employees with varying skills.

ExampleExample(many-to-many-to-many):(many-to-many-to-many):

Employee

Project

Skill

Has

The Relational Data ModelThe Relational Data Model

ComponentsComponents E-R Models to RelationsE-R Models to Relations

Relational Data Model Relational Data Model ComponentsComponents Data structureData structure Data manipulationData manipulation Data integrityData integrity

Data StructureData Structure

Tables with rows and columnsTables with rows and columns

Two-dimensionalTwo-dimensional Column – AttributeColumn – Attribute Row – single instance of an entity – each Row – single instance of an entity – each

is uniqueis unique

Sequence is immaterial (rows or columns)Sequence is immaterial (rows or columns)

Data Structure – AlternativeData Structure – Alternative

TableTable -- RelationRelation

RowRow -- TupleTuple

ColumnColumn -- AttributeAttribute

(domain – all possible values)(domain – all possible values)

Database Schema – DefinitionDatabase Schema – Definition

For each table/relation:For each table/relation:

Name = (attribute, attribute, attribute, …)Name = (attribute, attribute, attribute, …)

Example:Example:

Employee = (employee_ID, name, dept#, Employee = (employee_ID, name, dept#, phone#)phone#)

Department = (dept#, dept_name)Department = (dept#, dept_name)

KeysKeys

Attributes can be either identifiers or Attributes can be either identifiers or descriptors.descriptors.

Identifier uniquely determines an Identifier uniquely determines an instance of an entity.instance of an entity.

Identifier is a key.Identifier is a key.

Types of KeysTypes of Keys

Superkey - any combination of attributes that Superkey - any combination of attributes that uniquely determines each instance of the uniquely determines each instance of the entities in an entity setentities in an entity set

Candidate Key - superkey for which no Candidate Key - superkey for which no proper subset is also a superkeyproper subset is also a superkey

Primary Key - selected candidate key used Primary Key - selected candidate key used to identify each row (tuple)to identify each row (tuple)

Types of KeysTypes of Keys

Superkeys Superkeys Candidate Keys Candidate Keys Primary Key Primary Key

Primary key

Candidate keys

Superkeys

Composite KeyComposite Key

Key that consists of more than one Key that consists of more than one attribute.attribute.

Example: first name Example: first name andand last name last name

Example:Example:

Course (course#, name, dept#, location, Course (course#, name, dept#, location, time)time)

Superkeys?Superkeys?

Candidate Keys?Candidate Keys?

Primary Key?Primary Key?

Database Schema (Updated)Database Schema (Updated)

Underline primary key(s)Underline primary key(s)

Name = (Name = (keykey, attribute, attribute, , attribute, attribute, attribute, …)attribute, …)

Example:Example:

Employee = (Employee = (employee_IDemployee_ID, name, , name, dept#, phone#)dept#, phone#)

Department (Department (dept#dept#, dept_name), dept_name)

Foreign KeysForeign Keys

Attribute in a relation that serves as Attribute in a relation that serves as the primary key of another relation in the primary key of another relation in the same database.the same database.

Used to maintain database integrity – Used to maintain database integrity – can’t sell a product that isn’t in the can’t sell a product that isn’t in the inventory, etc.inventory, etc.

Database Schema (Updated)Database Schema (Updated)

Dashed underline foreign key(s)Dashed underline foreign key(s)Name = (Name = (keykey, attribute, attribute (FK), , attribute, attribute (FK),

attribute, …)attribute, …) Example:Example:

Employee = (Employee = (employee_IDemployee_ID, name, , name, dept#, phone#)dept#, phone#)

Department (Department (dept#dept#, dept_name), dept_name) Could also use double underline (PK)Could also use double underline (PK)

Multivalued AttributesMultivalued Attributes

Attributes that repeat (once or more) for a Attributes that repeat (once or more) for a single entity.single entity.

Enclosed in braces {}Enclosed in braces {}Name = (Name = (keykey, attribute, attribute (FK), , attribute, attribute (FK), {attribute, attribute}, …){attribute, attribute}, …)

Example:Example:Employee = (Employee = (employee_IDemployee_ID, name, dept#, , name, dept#,

{phone#, type}){phone#, type})Department (Department (dept#dept#, dept_name), dept_name)

Converting an E-R Model to a Converting an E-R Model to a Database SchemaDatabase Schema1.1.Strong Entities become RelationsStrong Entities become Relations

Identify the primary key from the Identify the primary key from the superkeys and candidate keys.superkeys and candidate keys.

Converting an E-R Model to a Converting an E-R Model to a Database SchemaDatabase Schema2. Weak Entities become Relations2. Weak Entities become Relations

The primary key of a weak entity will The primary key of a weak entity will usually have to include the primary usually have to include the primary key of the relation on which the weak key of the relation on which the weak entity depends along with additional entity depends along with additional identifying information for the weak identifying information for the weak entity.entity.

Converting an E-R Model to a Converting an E-R Model to a Database SchemaDatabase Schema3. Binary, Ternary, and n-ary Relationships3. Binary, Ternary, and n-ary Relationships

one-to-one or one-to-manyone-to-one or one-to-manyIdentify foreign keysIdentify foreign keys

many-to-many or associative entitiesmany-to-many or associative entitiesBuild a “bridge” relationship whose primary Build a “bridge” relationship whose primary key is a composite key composed of all key is a composite key composed of all the relationship’s entities primary keys. the relationship’s entities primary keys. These are also foreign keys.These are also foreign keys.

Converting an E-R Model to a Converting an E-R Model to a Database SchemaDatabase Schema4. Unary Relationships4. Unary Relationships

one-to-one or one-to-manyone-to-one or one-to-many

Identify “recursive” foreign keyIdentify “recursive” foreign key

many-to-manymany-to-many

Build a “bridge” relationship whose Build a “bridge” relationship whose primary key is a composite key composed primary key is a composite key composed of two copies of the entity’s primary key.of two copies of the entity’s primary key.

Converting an E-R Model to a Converting an E-R Model to a Database SchemaDatabase Schema5. Supertype/Subtype - Option 15. Supertype/Subtype - Option 1 Create separate relations for the supertype and each Create separate relations for the supertype and each

subtypesubtype Supertype contains all common attributes and the primary Supertype contains all common attributes and the primary

keykey Subtypes have the same primary key as the supertype Subtypes have the same primary key as the supertype

and only those attributes specific to that subtypeand only those attributes specific to that subtype Add to the supertype a subtype indicator. Only one Add to the supertype a subtype indicator. Only one

indicator is needed for disjoint. More that one is needed if indicator is needed for disjoint. More that one is needed if overlapping. If there is no completeness constraint, allow overlapping. If there is no completeness constraint, allow the indicator to be null.the indicator to be null.

Converting an E-R Model to a Converting an E-R Model to a Database SchemaDatabase Schema5. Supertype/Subtype - Option 25. Supertype/Subtype - Option 2 Create a separate relation for each subtype onlyCreate a separate relation for each subtype only Subtypes have overlapping attributes (those that are Subtypes have overlapping attributes (those that are

common and would have been put in the relation for common and would have been put in the relation for the supertype if it was created)the supertype if it was created)

If the primary key of the supertype is used as a If the primary key of the supertype is used as a foreign key in other relations beyond the subtypes, a foreign key in other relations beyond the subtypes, a primary key only relation for the supertype will need primary key only relation for the supertype will need to be created.to be created.

Converting an E-R Model to a Converting an E-R Model to a Database SchemaDatabase Schema5. Supertype/Subtype - Option 35. Supertype/Subtype - Option 3 Create one relationCreate one relation Attributes in this relation are a superset created from Attributes in this relation are a superset created from

the attributes for the supertype and all subtypes. the attributes for the supertype and all subtypes. (Null values must be permitted for the subtype (Null values must be permitted for the subtype attributes as only a few are used for any given tuple.)attributes as only a few are used for any given tuple.)

ExampleExample

BankingBanking

Exercise:Exercise:

Real Estate OfficeReal Estate Office

Homework #4Homework #4

Convert E-R Diagram to SchemaConvert E-R Diagram to Schema IdentifyIdentify

Primary keys (may be composite)Primary keys (may be composite) Foreign keysForeign keys

Dashed underline (not in PK)Dashed underline (not in PK) Double underline (in PK)Double underline (in PK)

Exam ReviewExam Review

DBMS - usage & alternativesDBMS - usage & alternatives Methodologies and peopleMethodologies and people E-R ModelingE-R Modeling

Entity (strong, weak, associative)Entity (strong, weak, associative) Relationships (degree, cardinality, Relationships (degree, cardinality,

connectivity, existence)connectivity, existence) Gen/SpecGen/Spec