IS 3400 Final Exam

Data

• Raw facts 

• ex) first name, last name

Information

• Data processed to reveal meaning to the user 

• ex) list of students having GPA > 3.0

Candidate Key

Determines all other columns in a relation and are your options for the PK

Primary Key (PK)

• Uniquely identifies a row/record

• Non repetitive 

• Must have a value

Surrogate Key

• An artificial column added to a relation to serve as a primary key 

• ex) StudentID

Foreign Key (FK)

• A key that connects a table to another table 

• Values can repeat

• May be empty

Composite Key

• Primary key is made of more than one column 

• ex) RENTAL_PROPERTY (Street, City, State, ZipCode, Country, Rental_Rate)

• Not great, use a surrogate key instead

Referential Integrity Constraint

A statement that limits the values of the foreign key to those already existing as primary key values in the corresponding relation 

Entity-Relationship Modeling Using Crow’s Foot Notation

One-to-One Maximum Cardinality

1:1

An employee has at the most ONE badge, a badge at the most has ONE employee assigned to it

One-to-Many Maximum Cardinality

1:N

An employee can have MANY computers assigned to them, a computer has at the most ONE employee assigned to it

Many-to-Many Maximum Cardinality

N:M

An employee can have MANY skills, a skill can be possessed by MANY employees

Mandatory-to-Mandatory Minimum Cardinality

And employee at the least has ONE badge, a badge at the least has one employee associated with it

Optional-to-Optional Minimum Cardinality

An employee may have ZERO computers, a computer may be assigned to ZERO employees

Optional-to-Mandatory Minimum Cardinality

An employee will have at least ONE skill, a skill may be possessed by ZERO employees

ID-Dependent Entities

• A child entity whose identifier includes the identifier of the parent entity 

• Denoted using a solid line 

• Weak entity 

• ex) EXAM (PaitientRecordNumber (PK), DateStamp (PK)) includes the PK of PATIENT (Patient RecordNumber (PK), FirstName, LastName, etc.) 

Non-ID-Dependent Entities

• Entities that are not connected with PK’s (regular tables)

• Denoted with dashed lines

• Strong entity

Weak Entities

An entity whose existence depends upon another entity

Strong Entities

An entity that represents something that can exist on its own

1:1 Entity Relationship Using Foreign Keys

The primary key from any side can be made a foreign key on the other side

1:N Entity Relationship Using Foreign Keys

The primary key on the “one” side is made a foreign key on the “many” side

N:M Entity Relationship Using Foreign Keys

Create a new table which will have foreign keys of the primary keys on the connecting tables

CREATE (SQL DDL Statement)

CREATE TABLE BUILDING (

BuildingCode CHAR (2) NOT NULL, 

CONSTRAINT BuildingPK PRIMARY KEY (BuildingCode)

);

INSERT (SQL DDL Statement)

INSERT INTO BUILDING (BuildingCode)

VALUES (‘EH’)

ALTER (SQL DDL Statement)

ALTER TABLE BUILDING

ADD TechEnabled CHAR (1) NULL;

DROP (SQL DDL Statement)

ALTER TABLE ROOM DROP CONSTRAINT _______