Knowt
CIS Chapter 1-4
Chapter 1
Operational and informational data
Operational
Things such as ho much money as made today
Informational
Things that can be present but also from the past
DB
Stands for Database
Organized collection of logically related data
Data
Stored representations of meaningful objects and events
Thing such as name or birthday
Structured
Numbers, text, dates
Unstructured
Images, videos, documents
DBA
Administrator
DBMS
Database management system
Data management
Personal
Things made for personal use
Multi tiered
For small companies
Like ord
Enterprise
Information
Data processed to increase knowledge in the person using the data
Processed is the keyword
The data has to be meaningful to be processed into information
Data is in Information is Out
Metadata
Data that describes the properties and context of user data
It is data about the data
Data Models
An abstract model that organizes data description, semantics, and consistency constraints of data
Graphs
Enterprise Data Model
Project Data Model
Data Modeling
The process of creating a data model for the data to be stord in a database
Types of Data Models in DBMS
Conceptual Data Model:
This Data Model defines WHAT the system contains. This model is typically created by Business stakeholders and Data Architects. The purpose is to organize, scope and define business concepts and rules.
Logical Data Model:
Defines HOW the system should be implemented regardless of the DBMS. This model is typically created by Data Architects and Business Analysts. The purpose is to developed technical map of rules and data structures.
Physical Data Model:
This Data Model describes HOW the system will be implemented using a specific DBMS system. This model is typically created by DBA and developers. The purpose is actual implementation of the database.
Entities
Describing a person, place, object, event, or concept
Contains Attribute/Field
Any information that can be used to identify you
Non-Key
Attributes that maychange
Primary Key
Attributes that are unique and most likely unchanged
Square
An strong entity
Round
Not original entity
Usually the bridge
Relationships
Parent
The one that influences the relationship
Child
The one that is influenced
Which is Parent and Child
Example
Does the customer make the order or the order makes the customer
In this case the customer ould be sent as a foreign key to the order entity
Solid underline
Signifies Primary Key
Dotted underline
Foreign key
What is th relationship between the entities
One to many
Many to one
One to one
Many to many
Hen dealing with this you have to have another entity in the middle of them
Called a bridge
It borrow both primary keys hen connecting many to many
Both primary keys together become one primary key
I and O
I mean mandatory
O means Optional
Examples
Each CUSTOMER Places any number of ORDERs. Conversely, each ORDER Is Placed By exactly one CUSTOMER.
Each ORDER Contains any number of ORDER LINEs. Conversely, each ORDER LINE Is Contained In exactly one ORDER.
Each PRODUCT Has any number of ORDER LINEs. Conversely, each ORDER LINE Is For exactly one PRODUCT.
Each ORDER Is Billed On one INVOICE and each INVOICE Is a Bill for exactly one ORDER.
ERD or ERM
Entity, Relationship, Data
Entity, Relationship, Model
Pine Valley Furniture Company
Conceptual
Creating the entities
Logical
Where you create the attributes and relationships for the entities
Physical
Cannot have many to many (remember for test)
Is physical when the creation of the bridge entity is created
Terms to know
Data modeling and design tools – automated tools used to design databases
and application programs
Repository – centralized storehouse of metadata
Database Management System (DBMS) – software for managing the database
Database – storehouse of the data
Application Programs – software using the data
User Interface – text, graphical displays, menus, etc. for user
Data/Database Administrators – personnel responsible for maintaining the database
System Developers – personnel responsible for designing databases and software
End Users – people who use the applications and databases
Chapter 2
E-R Model Constructs
Entity instances
Person, place, object, event, concept (often corresponds to a row in a table)
Example
Entity is Student
Entity instances are the many students
Relationships
If the primary key appears in another entity
DDL
DML
Attribute
A property of characteristic of an entity
Required attribute
An attribute that must be present from each entity instance
Example
Name
Maybe phone number or email
Are usually in bold
Optional attribute
An attribute that may not have a value
Example
Major for college students
Composite attribute
An attribute that has meaningful parts
Example
Things such as an employees address contains the address, city, state, postal code
Simple/Atomic attribute
An attribute that cannot be broken down into smaller components that are meaningful to the organization
Example
All attributes associated with Automobile
Color
Horsepower
Multivalued Attribute
An attribute that may take on more than one value for a given entity instance
Indicated ith curly brackets around the attribute name
Example
Skills
You can have multiple skills
Derived attribute
An attribute whose values can be calculated from related attribute values
Example
Time
Date
Identifier
An attribute whose value distinguishes individual instances of an entity type
Example
Primary and foreign key
Composite identifier
An identifier that consists of a composite attribute
Relationship type
A meaningful association between entity types
Relationship instance
An association between entity instances here each relationship instance associates exactly one entity instance
Associative entity
An entity that associates the instances of one of more entity types
Many to many bridge
Degree
The number of entity types that participates in a relationship
Cardinality constraint
A rule that specifies the number of instance of one entity that can or must be associated ith each instance of another entity
Example
A DVD store can have many stock of one DVD or none
Min and Max
Minimum cardinality
The minimum number of instances of one entity that can be associated ith each instance of another entity
Maximum Cardinality
The maximum instances of one entity that may be associated ith each instance of another entity
Time stamp
A time value that is associated ith a data value
Often indicated hen some event occurred that affected the data value
Pine valley Furniture Company
Chapter 3
Enhanced entity relationship (EER)
A model that has resulted from extending the original E-R model with new modeling constructs
Subtype
A subgrouping of entities in a entity type that is meaningful to the organization and shares common attributes or relationships that are distinct from other subgroupings
Example
Student
Undergraduate
Graduate
Supertype
A generic entity type that has a relationship with one or more subtypes
Example
Student would be the supertype to Undergraduate and Graduate
Attribute inheritance
A property by which subtype entities inherit values of all attributes and instances of all relationships of their supertype
Example
Under the Supertype Employee
The Billing Rate attribute will be the same as the subtype Consultant
How to determine the Supertype/Subtype relationships
There are attributes that apply to some but not all instances of an entity type
The instances of a subtype participate in a relationship that is unique to that subtype
Example
Patient
Out Patient
Resident Patient
Will have a bed assigned
Generalization
The process of defining a more general entity type from a set of more specialized entity types
Example
Cars, Trucks, and Motorcycles can all go under the entity Vehicle
Specialization
The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships
Completeness constraint
A type of constraint that addresses whether an instance of a supertype must also be a member of at least one subtype
Total specialization rule
A rule that specifies that each entity instance of a supertype must be a member of some subtype in a relationship
Double line
Example
A patient must be either a Outpatient or a Resident Patient
Partial specialization rule
A rule that specifies that an entity instance of a supertype is allowed not to belong to any subtype
Single line
Example
A vehicle can be a car or truck but does not have to be either
Disjointness Constraint
A constraint that addresses whether an instance of a supertype amy simultaneously be a member of two or more subtypes
Disjoint Rule
A rule that specifies that an instance of a supertype may not simultaneously be a member of two or more subtypes
Example
A patient can be either an Outpatient or a Resident Patient but not both at the same time
Overlap Rule
A rule that specifies that an instance of a supertype may simultaneously be a member of two or more subtypes
Example
A part made for something
Can be both a manufactured part and a purchased part
Subtype Discriminator
An attribute of a supertype whose values determine the target subtype or subtypes
Example
For the Employee entity the discriminator would be the Employee Type
Coded differently
H for Hourly
S for Salaried
C for Consultant
A supertype/subtype hierarchy
A hierarchical arrangement of supertypes and subtypes in which each subtype has only one supertype
Chapter 4
Data Structure
Tables (relations), ros, columns
Relation
Two dimensional table of data
Consists of ros (records) and columns (attribute
Requirements for a table to qualify as a relation
It must have a unique name
Every attribute value must be atomic (not multivalued, not composite
Every ro must be unique (can’t have to ros oth exactly the same values for all their fields)
Attributes (columns) in tables must have unique names
The order of the columns must be irrelevant
The order of the ros must be irrelevant
Composite Key
A primary key that consists of more than one attribute
Null
A value that may be assigned to an attribute hen no other value applies or hen the applicable value is unknown
Entity integrity rule
A rule that states that no primary key attribute may be null
Referential integrity constraint
A rule that stats that either each foreign key value must match a primary key value in another relation or the foreign key must be null
Well Structured relation
A relation that contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies
Anomaly
An error or inconsistency that may be result when a user attempts to update a table that contains redundant data
Three types of anomalies
Insertion
Suppose that we need to add a new employee, the primary key would be EmpID and CourseTitle
We would need to supply data for both EmpID and CourseTitle
This is an anomaly because the user should be able to create a new employee without having to supply data for the course
Deletion
Suppose that the data for employee number 140 as deleted from the table
This results in the loss of information for that employee completing a course
It also results in the loss of information that this course had an offering that completed that date
Modification
Suppose that employee number 100 gets a salary increase
E must record that increase in each of the ros for that employee (to occurrences)
Or the data will be inconsistent
Entities
Regular
Entities that have an independent existence and generally represent real world objects
Example
People and Products
Represented as a rectangle with a single line
Weak
An entity that cannot exist except with an identifying relationship with a regular entity
Dependent on regular entity
Represented as a rectangle with double lines
Associative
An entity that is formed from a many to many relationship
Represented by a rectangle with round corners
AKA the Bridge
Surrogate Primary key
A serial number or other system assigned primary key for relation
When it is created
There is a composite primary key
When the use of the natural primary key is inefficient (ask question about this)
When the natural primary key is reused and repeated so it may not be unique overtime
Recursive foreign key
A foreign key in a relation that references the primary key values of the same relation
Unary relationship/relation
Normalization
The process of decomposing relations with anomalies to produce smaller, well structured relations
Goals of normalization
Minimize data redundancy, this avoids anomalies and conserves storage space
Simplify the enforcement of referential integrity constraints
Make it easier to maintain data (insert, update, and delete)
Provide a better design that is an improved representation of the real world and a stronger basis for future growth
Normal Form
A state of relation that requires that certain rules regarding relationships between attributes are satisfied
First Normal Form
Any multivalued attributes that have been removed so that there is a single value
Second normal Form
Any partial functional dependencies have been removed
Entity whose non primary key attributes are dependent of the full primary key
Third Normal Form
Any transitive dependencies have been removed
Entity whose primary key attributes are not dependent on any other non primary key attribute
Functional Dependency
A constraint between two attributes where the value of one attribute determines the value of another
Determinant
The attribute on the left side of the arrow in a functional dependency
Candidate key
An attribute or combination of attributes that uniquely identifies a row in a relation
Properties of a candidate key
Unique identification
For every row, the value of the key must uniquely identify
Non Redundancy
No attribute in the key can be deleted without destroying the property of unique identification
