Ch. 2

Define attribute

A characteristic of an entity Ex: CUSTOMER can have following attributes: CUST_LNAME, CUST_FNAME, CUST_PHONE, etc. Attributes = fields

Advantages of conceptual model/schema

Birds eye view of data environment, easy to understand Independent of hardware or software

"A customer may generate many invoices" "An invoice is generated by one and only one customer" "A training session cannot be scheduled for fewer than 10 employees or for more than 30 employees" These are all examples of what?

Business rules

Logical independence

Change internal model without affecting conceptual model

Sources of business rules

Company managers Policy makes Department managers Written documentation Direct interviews with end users

What is a conceptual model/schema?

Complete design.... Integrates all external views (entities, relationships, constraints, processes) into single global view of data On paper or visio

How does a conceptual model differ from a logical model?

Conceptual is more simple, logical lists data types, primary

Example of a Catholic church's business rule for modeling marraige

Constraint is 1:1 1 man marries 1 woman, vice-versa

Examples of a 1:M relationship

Customer places order (one customer can place many orders, but each order can only be placed by one customer) A tree can have many apples, but each apple belongs to one tree

What is an ER model (ERM)?

Data model that describes relationships among entities at the conceptual level (synopsis, outline, diagram) Represented by an Entity Relationship Diagram (ERD)

What is a relational schema?

Database model design Textual representation of table/tables Ex: CUSTOMER (CUST_ID, CUST_LNAME, CUST_STREET) ORDER (ORD_ID, ORD_DATA, ORD_TOTAL, CUST_ID)

How do business rules relate to DB design?

Define entities, relationships, and constraints Dictate how you want to go about modeling certain phenomenons

Physical model

Describes the way data are saved on storage media such a disks or tapes Operates at lowest level of abstraction Dependent on DBMS, methods of accessing files, and types of hardware storage devices supported by OS (hardware and software dependent)

What are business rules?

Descriptions of policies, procedures, or principles within a specific organization

An entity must be distinguishable. What does this mean?

Each entity occurence must be unique and distinct. Ex: CUSTOMER entity will have many distinguishable occurences

Examples of constraint

Employee salary must have values that are between 6,000 and 350,000 Students GPA must be between 0.00 and 4.00 Each class must have one and only one teacher

Building blocks of data model

Entities Attributes Relationships Constraints

Crow's Foot Notation

Entities represented by boxes with name at top Attributes under entity name Relationships represented by line between boxes, name of relationship above line Connectivites are symbols (||, |, 0|<, |<, etc)

In general, a noun in a business rule will translate into a _____ in the relational model and a verb that associates the nouns will translate to a ______ in the model.

Entity; relationship

Internal vs physical vs conceptual vs external model order of degrees of abstraction

External: highest Conceptual Internal Physical: lowest

Purpose of data model

Facilitate interaction among the designer, the applications programmer, and the end user End users have different views and needs for data, so the data model organizes data for various users Reduces complexities of database design Effectively a "blueprint" with all the instructions to build a database that will meet all end-user requirements

T/F: Business rules don't have to be in writing

False

T/F: Changes to the internal model aren't required after changes to DBMS software

False Internal models are software dependent

Why are constraints important?

Help ensure data integrity

Examples of a 1:1 relationship

Husband has one wife, wife has one husband (usually) One person has one passport (usually)

Internal vs Physical vs Conceptual vs External model dependencies

Internal: software dependent hardware independent Physical: software dependent hardware dependent External: hardware independent software independent Conceptual: hardware independent software independent

How did the relational model get its name?

It's foundation is a table, which can be known as a relation in mathematics. The relational model is based on mathematical set theory and represents data as independent relations (tables)

Internal Model

Maps conceptual model to DBMS Sort of "talks out" the conceptual model 2nd lowest degree of abstraction vs others Hardware independent, software dependent

What is a relation?

Matrix composed of intersecting rows and columns Sometimes called a table

What are the three types of relationships

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

Chen notation

Original ERM notation Relationships represented by diamond (with relationship name inside) connected to related entities through relationship line Entities represented by boxes with name inside Connectivities written next to each entity box

Define entity

Person, place, thing or event about which data will be collected and stored

Relational diagram

Representation of entities, attributes, and relationships Sits inside database

What is a relational model?

Represents data through independent relations, related to each other through the sharing of common entity characteristics (values in columns) Each relation (table) consists of row/columns, implemented through relational data management system (RDBMS) to hide its complexity from the user

Define constraint

Restriction placed on the data Expressed in form of rules May result from business rules

What is a logical model?

Similar to conceptual, but more complex in that it also models info from business requirements. Lists data types, primary/foreign keys

What is a data model?

Simple representation (usually graphical) of more complex real-world data structures Within database environment, model represents data structures and their characteristics, relations, constraints, transformations, and other constructs NOT database model... A database model is the implementation of a data model in a specific database system

Why are business rules essential to DB design?

Standardize company's view of data Communications tool between users and designers Allow designer to understand nature, role, and scope of data Allow designer to understand business processes Allow designer to develop appropriate relationship rules and constraints for accurate data model

Examples of a M:N relationship

Student enrolls in 0,1, or many classes and classes can have 0,1, or many students Each customer can have multiple addresses, each address can have multiple customers

Are there other names for a relation?

Table

T/F: the ER model is the most widely used conceptual model

True

T/F: Relationships are bi-directional

True Ex: CUSTOMER places ORDER (1:M) Customer can place 0,1,M orders, orders can only have 1 customer

T/F: There are multiple types of data models

True Logical models include entities, relationships, attributes Physical include entities, attributes, relationships, data types,

Physical independence

When you can change the physical model without affecting internal model Change in storage devices/methods/OS will not affect internal model

External model

end user's view of the data environment Ex: ER diagrams Highest level of abstraction vs others Hardware and software independent

In a relational schema, P-keys are _______ and F-keys are ________. Attributes are separated by _______.

underlined; italicized; commas