WEEK 2 Flashcards
(26 cards)
Importance of Data Models
Are a communication tool
Give an overall view of the database Organize data for various users
Are an abstraction for the creation of good database
Data Model Basic Building Blocks
ENTITY
ATTRIBUTE
RELATIONSHIP
A person, place, thing, concept or event about which data will be collected and stored
Example: customer
Entity:
A characteristic of an entity
Example: last name or first name of a customer
Attribute:
Describes an association among entities
Relationship:
RELATIONSHIP:
One-to-many (1:M or 1..*)
Many-to-many (M:N, M:M or ..)
One-to-one (1:1 or 1..1)
example:
Painter paints painting
EMPLOYEE leanrs SKILL
EMPLOYEE manages STORES
a restriction placed on the data, usually expressed in the form of rules
ensure data integrity
Examples:
An employee’s salary must have values between 6,000 and 350,000
A student’s GPA must be between 0.00 and 4.00
Each class must have one and only one teacher.
Constraint:
is a brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization.
Examples:
A customer may generate many invoices.
An invoice is generated by only one customer.
A training session cannot be scheduled for fewer than 10 employees or for more than 30 employees.
business rule
Business rules establish entities, relationships, and constraints.
Sources of Business Rules
Company managers
Policy makers
Department managers
Written documentation
Direct interviews with end users
Student and Class
In how many classes can one student enroll? Many
How many students can enroll in one class? Many
Many-to-many
Customer and Invoice
One customer may generate many invoices.
An invoice is generated by only one customer.
One-to-many
Introduced by E.F. Codd in 1970
The Relational Model
Matrix composed of intersecting tuple and attribute
Tuple: Rows
Attribute: Columns
Relation or table:
A Logical View of Data
Relational database model enables logical representation of the data and its relationships
Logical simplicity yields simple and effective database design methodologies
Facilitated by the creation of data relationships based on a logical construct called a relation
A Logical View of Data
Relational database model enables logical representation of the data and its relationships
Logical simplicity yields simple and effective database design methodologies
Facilitated by the creation of data relationships based on a logical construct called a relation
The Relational Model
Table schema:
Book (Bid: char(2), Title: char(50), Author: char(20), Price: decimal(5,2))
The Relational Model
Table schema:
Book (Bid: char(2), Title: char(50), Author: char(20), Price: decimal(5,2))
Consist of one or more attributes that determine other attributes
Used to:
Ensure that each row in a table is uniquely identifiable
Establish relationships among tables and to ensure the integrity of the data
KEYS
Attribute or combination of attributes that uniquely identifies any given row
Primary key (PK):
: Key that is composed of more than one attribute
Composite key
Attribute that is a part of a key
Key attribute:
Condition in which each row in the table has its own unique identity
All of the values in the primary key must be unique
No key attribute in the primary key can contain a null (cannot be left empty)
Entity integrity:
Absence of any data value that could represent:
An unknown attribute value
A known, but missing, attribute value
A inapplicable condition
Null:
Every reference to an entity instance by another entity instance is valid
refers to the relationship between tables. Because each table in a database must have a primary key, this primary key can appear in other tables because of its relationship to data within those tables. When a primary key from one table appears in another table, it is called a foreign key .
It is important because it ensures that all data in a database remains consistent and up to date. It helps to prevent incorrect records from being added, deleted, or modified.
Referential integrity:
Key used strictly for data retrieval purposes
For example, using your phone number, when you don’t have your customer ID
Secondary key:
An attribute or combination of attributes that uniquely identifies each row in a table
Superkey
A minimal (irreducible) superkey; a superkey that does not contain a subset of attributes that is itself a superkey
Candidate key
