1 r. ching, ph.d. mis california state university, sacramento week 8 october 19 database...

1

R. Ching, Ph.D. • MIS • California State University, Sacramento

Week 8Week 8October 19October 19

• Database DesignDatabase Design• Modeling with ERDModeling with ERD

2


AdministrationAdministration

• Data Administrator (DA) – management of the data Data Administrator (DA) – management of the data resources, including the database planning, development, resources, including the database planning, development, and maintenance of standards, policies and procedures, and maintenance of standards, policies and procedures, and conceptual and logical database designand conceptual and logical database design

• Database Administrator (DBA) – management of the Database Administrator (DBA) – management of the physical realization of a database system, including physical realization of a database system, including physical database design and implementation, setting physical database design and implementation, setting security and integrity controls, monitoring system security and integrity controls, monitoring system performance, and reorganizing the database (when performance, and reorganizing the database (when necessary)necessary)

3


Database DesignDatabase Design

• Data modelingData modeling

– Understanding the meaning of dataUnderstanding the meaning of data

• Identify the user’s perspective of dataIdentify the user’s perspective of data

• Identify the data themselvesIdentify the data themselves

• Identify the applications supported by the dataIdentify the applications supported by the data

– Communication information requirementsCommunication information requirements

• Diagram with ERD (entity-relationship diagram)Diagram with ERD (entity-relationship diagram)

Satisfying the Satisfying the informationinformation needs of the organization needs of the organization

4


Optimal Logical Design CriteriaOptimal Logical Design Criteria

• Structural validity - reflects the enterpriseStructural validity - reflects the enterprise

• Simplicity - ease of understandingSimplicity - ease of understanding

• Expressability - distinguishability of dataExpressability - distinguishability of data

• Nonredundancy - exclusion of extraneous informationNonredundancy - exclusion of extraneous information

• Shareability - nonexclusive dataShareability - nonexclusive data

• Extensibility - support future information requirementsExtensibility - support future information requirements

• Integrity - consistency with organization’s information use Integrity - consistency with organization’s information use and managementand management

• Diagrammatic representation - ability to graphically model Diagrammatic representation - ability to graphically model datadata

5


Logical vs. Physical DesignLogical vs. Physical Design

• LogicalLogical

– Defines the Defines the whatwhats (e.g., s (e.g., whatwhat information needs to be information needs to be present)present)

• PhysicalPhysical

– Defines the Defines the howhows (e.g., s (e.g., howhow data will be stored) data will be stored)

HowHowHowHowWhatWhatWhatWhat

SequenceSequence

6


Fact-Finding TechniquesFact-Finding Techniques

• Examining documentsExamining documents

• InterviewingInterviewing

• Observing the enterprise in operationObserving the enterprise in operation

• ResearchResearch

• QuestionnairesQuestionnaires

7


Design ToolsDesign Tools

Relational database designRelational database design

• Entity relationship diagram (ERD)Entity relationship diagram (ERD)

– Relations, relationships, constraintsRelations, relationships, constraints

• Data normalizationData normalization

– Method for establishing relationsMethod for establishing relations

For relational For relational model onlymodel only

For relational For relational database onlydatabase only

8


Data Modeling: Entity Relationship ModelingData Modeling: Entity Relationship Modeling

9


Entity Relationship (ER) ModelEntity Relationship (ER) Model(applies to relational data model)(applies to relational data model)

• High-level conceptual model High-level conceptual model

– Describes the structure of the database, and the Describes the structure of the database, and the associated retrieval and update transactions on the associated retrieval and update transactions on the databasedatabase

– Composed of Composed of

• Entity types Entity types

• Relationship typesRelationship types

• AttributesAttributes

10


ER ModelingER Modeling

Productsstock numberproduct descriptionretail pricestock on handstock on order

Manufacturersmanufacturer codemanufacturer name

AttributesAttributes

Relationship typeRelationship type

Entity typeEntity type

HaveHave0..*0..* 1..11..1

11


ER ModelingER ModelingAlternativelyAlternatively

ProductsStock numberProduct descriptionRetail priceStock on handStock on order

ManufacturersManufacturer codeManufacturer name




12


ERD NotationERD Notation

Music_categoriesMusic_categories

music_category_code {PK}music_category_code {PK}music_category_titlemusic_category_title

CDsCDs

stock_number {PK}stock_number {PK}CD_titleCD_titleartistartistmusic_category_code music_category_code record_label_coderecord_label_code

Entity typeEntity typePrimary keyPrimary key


Relationship nameRelationship name

ClassifyClassify

1..11..1 0..*0..*


Degree of the Relationship: BinaryDegree of the Relationship: Binary

Multiplicity (constraint)Multiplicity (constraint)

13


ERD NotationERD NotationAlternativelyAlternatively


Music_category_codeMusic_category_codeMusic_category_titleMusic_category_title

CDsCDs

Stock_numberStock_numberCD_titleCD_titleArtistArtistMusic_category_code Music_category_code Record_label_codeRecord_label_code

ClassifyClassify

Entity typeEntity typePrimary key (underscored)Primary key (underscored)




ManyMany(crows feet)(crows feet)

CardinalityCardinality

ZeroZero(circle)(circle)

MinimumMinimum(inside)(inside)MaximumMaximum

(outside)(outside)

14


1. Entity Types1. Entity Types

• Strong Entity TypeStrong Entity Type

– Not existence-dependent on another entity typeNot existence-dependent on another entity type

• Weak Entity TypeWeak Entity Type

– Existence-dependent on another entity type (i.e., child, Existence-dependent on another entity type (i.e., child, dependent, subordinate)dependent, subordinate)

Entity type Entity type Entity type Entity type

EntityEntityEntityEntityEntityEntityEntityEntity

EntityEntityEntityEntity Uniquely identifiableUniquely identifiable

15


Entity TypesEntity Types



CDsCDs


ClassifyClassify

Strong entity?Strong entity?

16


Definition of a Weak Entity TypeDefinition of a Weak Entity Type

“ “An entity type that borrows all or part of its primary key. An entity type that borrows all or part of its primary key. Identifying relationships indicate the entity types that Identifying relationships indicate the entity types that supply components of the borrowed primary key.”supply components of the borrowed primary key.”

Mannino, 1999Mannino, 1999Entity type Entity type 11

KeyKeyattributes...attributes...

Entity type Entity type 11

KeyKeyattributes...attributes...


KeyKeyKeyKeyattributes...attributes...


KeyKeyKeyKeyattributes...attributes...

Composite keyComposite key

Weak entity typeWeak entity type

Method to FollowMethod to FollowMethod to FollowMethod to FollowHaveHave

17


Diagramming Weak Entity TypesDiagramming Weak Entity Types

Strong entity type (parent, Strong entity type (parent, owner, dominant)owner, dominant)

Weak entity entity (child, dependent, Weak entity entity (child, dependent, subordinate)subordinate)

An account cannot exist without an customer.An account cannot exist without an customer.

*A customer can have more than one account*A customer can have more than one account

CustomersCustomers

attributes...attributes...

CustomersCustomers


Customer_AccountsCustomer_Accounts




Designates a weak entity typeDesignates a weak entity type

Minimum must be oneMinimum must be one

18


2. Attributes2. Attributes

Property of an entity or relationship typeProperty of an entity or relationship type

• Attribute domainAttribute domain

– Set of values that may be assigned to a single-valued Set of values that may be assigned to a single-valued attributeattribute

Cust_account Cust_name Cust_address Cust_phone Soc_Sec_NumCust_account Cust_name Cust_address Cust_phone Soc_Sec_NumCust_account Cust_name Cust_address Cust_phone Soc_Sec_NumCust_account Cust_name Cust_address Cust_phone Soc_Sec_Num

CustomersCustomers

Cust_account Current_balance Credit_limit Active_date Expire_dateCust_account Current_balance Credit_limit Active_date Expire_dateCust_account Current_balance Credit_limit Active_date Expire_dateCust_account Current_balance Credit_limit Active_date Expire_date


19


Attributes of AttributesAttributes of Attributes

• Simple (atomic attributes) - composed of a single Simple (atomic attributes) - composed of a single componentcomponent

• Composite - composed of multiple componentsComposite - composed of multiple components• Single valued - one value for an entitySingle valued - one value for an entity• Multi-valued - one or more values for an entityMulti-valued - one or more values for an entity• Derived - value derived from a related attribute or set of Derived - value derived from a related attribute or set of

attributesattributes

Student_ID Semester Course_IDStudent_ID Semester Course_IDStudent_ID Semester Course_IDStudent_ID Semester Course_ID

Student_ID FName MName Student_ID FName MName LNameLNameStudent_ID FName MName Student_ID FName MName LNameLName

Multi-valuedMulti-valued

Single-valuedSingle-valued

More than one More than one semestersemester, more than one , more than one course_idcourse_id

20


Attribute DomainAttribute Domain

Cust_account Cust_name Cust_address Cust_phone Cust_account Cust_name Cust_address Cust_phone Soc_Sec_NumSoc_Sec_NumCust_account Cust_name Cust_address Cust_phone Cust_account Cust_name Cust_address Cust_phone Soc_Sec_NumSoc_Sec_Num

CustomersCustomers

JohnJohnWilliamWilliamAnitaAnitaHomerHomer

BrownBrownTellTellBreakeBreakeSimpsonSimpson

• On an ER model, On an ER model, should customer should customer name be shown as a name be shown as a composite or simple composite or simple attribute? attribute?

• What is the attribute What is the attribute domain of domain of Cus_name?Cus_name?

Cust_first_nameCust_first_nameCust_first_nameCust_first_name Cust_last_nameCust_last_nameCust_last_nameCust_last_name

CompositeComposite

21


Derived AttributesDerived Attributes

• Derived - value derived from a related attribute or set of Derived - value derived from a related attribute or set of attributesattributes

Units Units xx Grade = Grade point Grade = Grade point

Student_ID Semester Course_ID Units Grade Grade_pointStudent_ID Semester Course_ID Units Grade Grade_pointStudent_ID Semester Course_ID Units Grade Grade_pointStudent_ID Semester Course_ID Units Grade Grade_pointStudent_ID Semester Course_ID Units Grade Grade_pointStudent_ID Semester Course_ID Units Grade Grade_pointStudent_ID Semester Course_ID Units Grade Grade_pointStudent_ID Semester Course_ID Units Grade Grade_point

Student_ID Semester Course_ID Units Grade Grade_pointStudent_ID Semester Course_ID Units Grade Grade_pointStudent_ID Semester Course_ID Units Grade Grade_pointStudent_ID Semester Course_ID Units Grade Grade_point

22


Attributes as KeysAttributes as Keys

Primary keyPrimary keyPrimary keyPrimary key

Candidate keyCandidate keyCandidate keyCandidate key

• Alternate key - candidate that can also be used to access Alternate key - candidate that can also be used to access an entityan entity

• Composite key - composed of multiple attributes Composite key - composed of multiple attributes (components)(components)

Uniquely identifies an entityUniquely identifies an entity

• Keys cannot change their values Keys cannot change their values (good for the life of the entity)(good for the life of the entity)

• An An efficientefficient means for identifying means for identifying an entityan entity

23


Diagrammatic RepresentationDiagrammatic Representation

Composite attributeComposite attribute

CustomersCustomers

Cust_account {PK}Cust_account {PK}Cust_nameCust_name First_nameFirst_name Middle_nameMiddle_name Last_nameLast_nameCust_addressCust_address Street_numberStreet_number Zip_code (fk)Zip_code (fk)Cust_phoneCust_phoneSoc_sec_numSoc_sec_num

CustomersCustomers

Cust_account {PK}Cust_account {PK}Cust_nameCust_name First_nameFirst_name Middle_nameMiddle_name Last_nameLast_nameCust_addressCust_address Street_numberStreet_number Zip_code (fk)Zip_code (fk)Cust_phoneCust_phoneSoc_sec_numSoc_sec_num

Composite attributeComposite attribute

KeyKey

Foreign keyForeign key

24


3. Relationship Types3. Relationship Types

• A set of associations between A set of associations between two (or more) participating two (or more) participating entity typesentity types

• Each is given a name that Each is given a name that describes the functiondescribes the function

CustomersCustomersCustomer_accountCustomer_accountCustomersCustomersCustomer_accountCustomer_account

Own Own

Customers_accountsCustomers_accountsCustomer_accountCustomer_accountCustomers_accountsCustomers_accountsCustomer_accountCustomer_account

25


Entity Relationship DiagramEntity Relationship Diagram

• Degree of a relationship - Degree of a relationship - number of entities participating number of entities participating in a relationship (binary, ternary, in a relationship (binary, ternary, quaternary, etc.)quaternary, etc.)

RelationshipRelationship

• ““Dog-ear” lines indicate a Dog-ear” lines indicate a relationship between a weak and relationship between a weak and strong entitystrong entity

CustomersCustomersCustomer_accountCustomer_accountCustomersCustomersCustomer_accountCustomer_account

WeakWeak

StrongStrong

Own Own

Customers_accountsCustomers_accountsCustomer_accountCustomer_accountCustomers_accountsCustomers_accountsCustomer_accountCustomer_account

26


Data ModelingData Modeling

Strong Entity (parent)Strong Entity (parent)

Strong Entity (parent)Strong Entity (parent)

Weak EntityWeak Entity(child)(child)



CDsCDs

Stock_numberStock_numberCD_titleCD_titleArtistArtistMusic_category_code (fk)Music_category_code (fk)Record_label_code (fk)Record_label_code (fk)

Record_labelsRecord_labels

Record_label_codeRecord_label_codeRecord_labelRecord_label

ClassifyClassify

ProduceProduce

RelationshipRelationship

Method to FollowMethod to FollowMethod to FollowMethod to Follow

All children (CDs) must have a parent All children (CDs) must have a parent (music categories or record labels)(music categories or record labels)All children (CDs) must have a parent All children (CDs) must have a parent (music categories or record labels)(music categories or record labels)

27


Degree of a RelationshipDegree of a Relationship

CustomersCustomersCustomersCustomers

Cust_AccountsCust_AccountsCust_AccountsCust_Accounts

ProductsProductsProductsProducts

A customer purchases products and A customer purchases products and places them on his/her accountplaces them on his/her account

Relationship of degree three or ternaryRelationship of degree three or ternary

BuyBuy

28


Degree of a RelationshipDegree of a Relationship

EmployeesEmployees

Employee_numberEmployee_numberEmployee_nameEmployee_nameClassificationClassificationProject_IDProject_ID

ManagesManages

A manager manages A manager manages one to many employees one to many employees ((a manager is related a manager is related to a minimum of one to a minimum of one and a maximum of and a maximum of many employeesmany employees))

An employee is managed by only one manager An employee is managed by only one manager ((an employee is related to a maximum and an employee is related to a maximum and minimum of one managerminimum of one manager))

Self-referencing Self-referencing relationshiprelationship

29


Structural ConstraintsStructural Constraints

• CardinalityCardinality– Determines the number of possible relationships for Determines the number of possible relationships for

each participating entityeach participating entity• 1:1 - one to one1:1 - one to one• 1:M - one to many1:M - one to many• M:N - many to manyM:N - many to many

• ParticipationParticipation– Determines whether the existence of an entity depends Determines whether the existence of an entity depends

upon its being related to another entity through the upon its being related to another entity through the relationshiprelationship

Defined by Defined by business rulesbusiness rules

30



• 1:1 (1:1 (one to oneone to one))– Each entity in Each entity in XX is associated with at most one entity in is associated with at most one entity in YY

and conversely each entity in and conversely each entity in YY is associated with at most is associated with at most one entity in one entity in XX

• 1:M (1:M (one to manyone to many))– Each entity in Each entity in XX can be associated with many entities in Y can be associated with many entities in Y

but each entity in but each entity in YY is associated with is associated with at mostat most one entity one entity in in XX..

• M:N (many to many)M:N (many to many)– Each entity in Each entity in XX can be associated with many entities in can be associated with many entities in YY

and each entity in and each entity in YY can be associated with many entities can be associated with many entities in in XX..

31



CustomersCustomers

Customer_IDCustomer_IDCustomer_nameCustomer_nameCustomer_addressCustomer_addressZip_codeZip_code

OwnOwn

A customer owns a A customer owns a minimum and maximum minimum and maximum of of oneone account account

AccountsAccounts

Account_numberAccount_numberCustomer_IDCustomer_IDAccount_typeAccount_typeCurrent_balanceCurrent_balance

An account is owned An account is owned by a minimum and by a minimum and maximum of maximum of oneone customercustomer

1:1 Relationships1:1 RelationshipsStrong entity typeStrong entity type Weak entity typeWeak entity type

Mandatory Mandatory participationparticipation

NoteNote. This would be avoided in the logical design, but could be implemented in . This would be avoided in the logical design, but could be implemented in the physical.the physical.

32



CustomersCustomers


OwnOwn

A customer owns a A customer owns a minimum one and minimum one and maximum of many maximum of many accountsaccounts

AccountsAccounts


1:M Relationships1:M RelationshipsStrong entity typeStrong entity type Weak entity typeWeak entity type



An account is own by a An account is own by a minimum and maximum minimum and maximum of one customerof one customer

33



• M:N relationship if a customer can own more than one M:N relationship if a customer can own more than one account (e.g., revolving, long-term), and one account can account (e.g., revolving, long-term), and one account can have more than one owner (e.g., joint account).have more than one owner (e.g., joint account).

34



CustomersCustomers


OwnOwn

A customer owns a A customer owns a minimum of one and a minimum of one and a maximum of many maximum of many accountsaccounts

AccountsAccounts


An account is owned An account is owned by a minimum of one by a minimum of one and a maximum of and a maximum of many customersmany customers

M:N RelationshipsM:N RelationshipsStrong entity typeStrong entity type Weak entity typeWeak entity type



35


Participation ConstraintsParticipation Constraints

• Determines whether the existence of an entity depends on Determines whether the existence of an entity depends on it being related to another entity through the relationshipit being related to another entity through the relationship

– Total (mandatory) - If the existence of one requires Total (mandatory) - If the existence of one requires another another

– Partial (optional) - If the existence of one does not Partial (optional) - If the existence of one does not require the otherrequire the other

Existence Dependency: An entity that cannot exist unless another Existence Dependency: An entity that cannot exist unless another related entity exists. A mandatory relationship produces an existence related entity exists. A mandatory relationship produces an existence dependency.dependency.

Mannino, 1999Mannino, 1999

Existence Dependency: An entity that cannot exist unless another Existence Dependency: An entity that cannot exist unless another related entity exists. A mandatory relationship produces an existence related entity exists. A mandatory relationship produces an existence dependency.dependency.

Mannino, 1999Mannino, 1999

36





CDsCDs


ClassifyClassify









(outside)(outside)

A CD is related to a A CD is related to a minimum and maximum minimum and maximum of of oneone music category music category

A CD is related to a A CD is related to a minimum and maximum minimum and maximum of of oneone music category music category

37





CDsCDs


ClassifyClassify









(outside)(outside)

A music category is related A music category is related to a minimum of zero and to a minimum of zero and maximum of many CDsmaximum of many CDs

A music category is related A music category is related to a minimum of zero and to a minimum of zero and maximum of many CDsmaximum of many CDs

38





CDsCDs


ClassifyClassify


Weak entity typeWeak entity type(all four corners)(all four corners)

Minimum cardinality of oneMinimum cardinality of one((a music category has to have at least one CDa music category has to have at least one CD))

39





CDsCDs


ClassifyClassify



ProduceProduce

A record label is related to A record label is related to a minimum of zero and a minimum of zero and maximum of many CDsmaximum of many CDs

A record label is related to A record label is related to a minimum of zero and a minimum of zero and maximum of many CDsmaximum of many CDs

40





CDsCDs


ClassifyClassify



ProduceProduceA CD is related to a A CD is related to a minimum and maximum of minimum and maximum of one record labelone record label

A CD is related to a A CD is related to a minimum and maximum of minimum and maximum of one record labelone record label

41





CDsCDs


ClassifyClassify



ProduceProduceQuantity_producedQuantity_produced

Attribute of a relationshipAttribute of a relationship

42


1 r. ching, ph.d. mis california state university, sacramento week 8 october 19 database...

Documents