Week 2

Entity supertypes and subtypes Supertype can have ______________

many subtypes

Entity instance or entity occurrence they are ______ in the _________

Rows Relational Table

Data Models

Simple representation of complex real-data structure

Dara models are important for supporting a ______________

Specific domain problem

Entity Supertypes and Subtypes Criteria to determine the usage The different kinds of instances should each have _______ or _______ that are unique to that_____________

one or more attributes kind of instance

Allows designers to Develop appropriate relationship_________ n ________

participation rules and constraints

The most important characteristic of an entity is its ___________.

primary key

Weak Entity The entity has a___________ that is _________ or _______from the _________ in the _____________.

primary key partially or totally derived parent entity relationship

Composite Keys they are ___________ with more than _________

primary keys one attribute

Connectivity they are terms used to label the ___________

relationship types

Primary keys need to be secure. The selected primary key must not be composed of any attributes that might be considered a ___________ Eg: __________

security risk. SSN

Specialization Hierarchy provides the means to define a _______________attribute known as the ______________

special supertype subtype discriminator

Entity supertypes and subtypes Subtype exists within the context of a __________

supertype

Entity supertypes and subtypes Every subtype has one ______to which it is___________

supertype directly related

Selecting Primary Keys

they should be 1. Unique 2. No change over time 3. Preferably single attribute 4. Preferably numeric 5. Security

Entity they are _________ and _________ used to _______ and _______

unique and distinct object collect and store data

Entity subtype contains _________ of each______

unique characteristics entity subtype

Preferably numeric: _____________ can be better managed when they are ___________

unique values numeric

Primary keys have ________. A primary key must be able to guarantee ______. It cannot contain ________.

unique values unique values nulls

Surrogate primary key is an "artificial" primary key that is used to ____________ each occurrence when there is ______________

uniquely identify no good natural key available.

Reasons for identifying and documenting business rules 2. Communication tool between______ and ____

user and designer

The surrogate key has no meaning in the __________ it exists only to distinguish __________ from ________. It is just like any other ___________

users environment one entity instance another primary key

Reasons for identifying and documenting business rules 1. helps standardize company's _______

view of data

_________ is a characteristic of entity

Attribute

Answer 2 Write the business rules reflected in this ERD.

·A store may place many orders. (Note the use of "may" - which is reflected in the ORDER optionality.) ·An order must be placed by a store. (Note that STORE is mandatory to ORDER) ·An order contains at least one order line. ·Each order line is contained in one and only one order. ·Each order line has a specific product written in it. ·A store may employ many employees. ·An employee may have one or more dependents. ·A dependent must be related to an employee.

Entity supertypes and subtypes relationships are described in terms of _______________

"is-a" relationships

Composite primary keys are particularly useful in two cases:

1. As identifiers of composite entities, where each primary key combination is allowed only once in the M:N relationship. 2. As identifiers of weak entities, where the weak entity has a strong identifying relationship with the parent entity.

Steps for designing a data structure

1.Identify the data elements •Interview stakeholders, past databases, similar databases, use cases 2.Subdivide each element into its smallest useful components 3.Identify tables and assign columns (attributes) 4.Identify primary key, foreign key, and relationships 5.Create the ERDs 6.Normalize your data structure 7.Revise the ERDs if needed 8.Create the database in DBMS 9.Identify the indexes 10.Test the database and its functionality

Criteria to determine the usage

<There must be different, identifiable kinds of the entity in the user's environment <The different kinds of instances should each have one or more attributes that are unique to that kind of instance

Allows designers to

<Understand the nature, role, scope of data, and business processes <Develop appropriate relationship participation rules and constraints <Create an accurate data model

Weak Relationship

A weak relationship exists when the PK of the related entity does not contain at least one of the PK attributes of the parent entity. A weak relationship is indicated by a dashed line in the ERD.

Multi-valued Attribute

An attribute that can have many values for a single occurrence

Sources of Business Rules

Company managers Department Managers Written Documentation Direct interviews with end users

Surrogate Key Example

DATE, TIME_START, ROOM) or (DATE, TIME_END, ROOM)

Derived attributes

Derived attributes are attributes whose value can be calculated from related attribute values.

Eg of Multi-Valued Attribute

EMP_ DEGREE attribute might store the string "BBA, MBA, PHD" to indicate three different degrees held

What are the basic building blocks of Data

Entity Relationship Constraint

Primary key main function is to uniquely identify an________ or _____within a table

Entity instance Raw

Primary keys cannot change overtime for example

If Sarah smith is a primary key what is going to happen when she gets married and changes her name to Sara Hagos?

What makes an entity weak?

It has two conditions 1. The entity is existence-dependent; it cannot exist without the entity with which it has a relationship. 2. The entity has a primary key that is partially or totally derived from the parent entity in the relationship.

_____________ it is a task of creating a conceptual data model

Logical Design

Data Modeling and Data Models

Models: they are abstraction of real-world object or event Data Models: simple representations of complex real world data structures. They are useful supporting a specific problem domain Data Modeling: iterative and progressive process of creating a specific data model for a determined problem domain

Relationship includes

One to many (1:M Many to many (M:N or M:N) One to one (1:1)

Weak Relationship exists when the_______ of the related entity does not contain at least ___________ of the __________

PK one of the PK attributes parent entity

_______ n ___________ are used to implement relationships among entities

Primary keys and foreign keys

Primary Keys

The most important characteristic of an entity is its primary key. A primary key is a single attribute or some contribution of attributes which uniquely identifies each entity instance Its main function is to uniquely identify an entity instance or row within a table Primary key and foreign keys are used to implement relationships among entities

Importance of Data Models

They are communication tool They give an overall view of the database Organize data for various users They are abstraction for the creation of good database

Allows designers to create an _____________

accurate data model

·Entity supertype: Contains __________

common characteristics

Relationship it describes an________________

association among entities

Specialization hierarchy provides the means to support __________________

attribute inheritance

Business Rules 2. Enable defining the ____________

basic building blocks

Business Rules 1. they are _______, ________, and ___________________ of _______, _______or_____

brief, precise and unambiguous description of policy Policy, Procedure or Principle

Composite primary keys are particularly useful in two cases: 1. As identifiers of ____________, where each primary key combination is allowed only once in the _______________. 2. As identifiers of___________, where the weak entity has a ____________ with the__________

composite entities M:N relationship weak entities strong identifying relationship parent entity

A weak relationship is indicated by a ________ in the __________

dashed line ERD

Constraints are sets of rules to ensure _____________

data integrity

A surrogate key is a primary key created by the __________________ to simplify the ______________

database designer identification of entity instances

Conceptual Schema: it is the basis for the __________ and ______ of the __________

definition and high level description main data objects

Entity Supertypes and Subtypes Criteria to determine the usage There must be _______, identifiable kinds of the _______in the _________

different entity users environment

Specialization Hierarchy Provides the means to define ________________ n _______________

disjoint/overlapping constraints and complete/partial constraints

The Entity Relationship Model Graphical representation of ________and their relationship in_____________

entities database structure

Weak Entity The entity is ___________; it cannot exist without the entity with which it has a __________.

existence-dependent relationship

Entity Supertype they are _________ type related to ______ or ______

generic entity one or more entity subtypes

The Conceptual Model it represents a _________ of the _______ by the ______________

global over view entire database entire organization

Entity Relationship Diagram (ERD) they use___________ to model __________

graphic representation database components

A specialization hierarchy depicts the arrangement of ____________ supertypes/parent entities and ___________/____________

higher-level entity lower-level entity subtypes/child entities

Entity Supertypes and subtypes depicts arrangement of_____________ and _______________

higher-level entity supertypes lower-level entity subtypes.

The conceptual method it is software and hardware _______

independent

One practical advantage of a surrogate key is that because it has no ___________, values for it can be generated by the _________to ensure that unique values are always _________

intrinsic meaning DBMS provided

Recursive Relationship ·A recursive relationship is one in which a relationship can exist between __________________. For example, a 1:M unary relationship can be expressed by

occurrences of the same entity set an EMPLOYEE may manage many EMPLOYEEs, and each EMPLOYEE is managed by one EMPLOYEE."

Primary Key

it is a single attribute or some combination of attributes which uniquely identifies each entity instances

Model

it is abstraction of the real word object or event

Data Modeling ________ and ________ of creating a specific data model for a determined _____________

iterative and progressive process problem domain

The conceptual method it has a _________ of data environment

macro-level view

Business Rules 3. Describe ___________ and _________

main and distinguishing characterstics of the data

Allows designer to Understand the ___________, __________, __________ n ___________

nature, role, scope of data and business processes

Preferably single-attribute Single-attribute primary keys are desirable but_________. Single-attribute primary keys simplify the ________of __________.

not required implementation foreign keys

Surrogate Key

·A surrogate primary key is an "artificial" PK that is used to uniquely identify each entity occurrence when there is no good natural key available ·A surrogate key is a primary key created by the database designer to simplify the identification of entity instances. ·The surrogate key has no meaning in the user's environment—it exists only to distinguish one entity instance from another (just like any other primary key). ·One practical advantage of a surrogate key is that because it has no intrinsic meaning, values for it can be generated by the DBMS to ensure that unique values are always provided.

Entity Supertypes and Subtypes

·Depicts arrangement of higher-level entity supertypes and lower-level entity subtypes ·Relationships are described in terms of "is-a" relationships ·Subtype exists within the context of a supertype ·Every subtype has one supertype to which it is directly related ·Supertype can have many subtypes

Entity Supertypes and Subtypes

·Entity supertype: Generic entity type related to one or more entity subtypes <Contains common characteristics ·Entity subtype: Contains unique characteristics of each entity subtype ·Criteria to determine the usage <There must be different, identifiable kinds of the entity in the user's environment <The different kinds of instances should each have one or more attributes that are unique to that kind of instance

Data Model Basic Building Blocks

·Entity: Unique and distinct object used to collect and store data <Attribute: Characteristic of an entity ·Relationship: Describes an association among entities <One-to-many (1:M) <Many-to-many (M:N or M:M) <One-to-one (1:1) Constraint: Set of rules to ensure data integrity

The Entity Relationship Model

·Graphical representation of entities and their relationships in a database structure ·Entity relationship diagram (ERD) <Uses graphic representations to model database components ·Entity instance or entity occurrence <Rows in the relational table ·Connectivity: Term used to label the relationship types

Reasons for Identifying and Documenting Business Rules

·Help standardize company's view of data ·Communications tool between users and designers ·Allow designer to: <Understand the nature, role, scope of data, and business processes <Develop appropriate relationship participation rules and constraints <Create an accurate data model

The Conceptual Model

·Represents a global view of the entire database by the entire organization ·Conceptual schema: Basis for the identification and high-level description of the main data objects ·Has a macro-level view of data environment ·Is software and hardware independent ·Logical design: Task of creating a conceptual data model

Selecting a Primary Key

·Unique values: A primary key must be able to guarantee unique values. It cannot contain nulls. ·No change over time: If Vickie Smith is the primary key, what happens if she changes her name when she gets married? ·Preferably single-attribute: Single-attribute primary keys are desirable but not required. Single-attribute primary keys simplify the implementation of foreign keys. ·Preferably numeric: Unique values can be better managed when they are numeric. ·Security: The selected primary key must not be composed of any attribute(s) that might be considered a security risk. Example: SSN