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