Prelims Flashcards
Enumeration
The purpose of a database
To store data
To provide an organizational structure for data
To provide a mechanism for querying, creating, modifying, and deleting data
Enumeration
Main problems with list
- Redundancy (the same information may be entered several times)
- Multiple themes (each row may contain more than one business concept)
Enumeration
Redundancy and Multiple themes create modification problems:
Deletion problems
Update problems
Insertion problems
Identification
is an international standard language for creating, processing and querying databases and their tables
Structured Query Language (SQL)
Identification
This ensures that the values of a column in one table are valid based on the values in another table
Referential Integrity constraints
Identification
is one or more columns of a relation whose values are used to identify a row
Key
Identification
Composed of two or more attributes
Composite Key
Identification
It has the potential to become the primary key
Candidate key
Identification
A candidate key chosen to be the main for the relation
Primary key
Identification
A unique, numeric value, that is added to a relation to serve as the primary key
Surrogate key
Identification
A primary key from one table that is placed into another table
Foreign key
Identification
states that every value of a foreign key must match the value of an existing primary key
Referential integrity
Identification
A relationship between attributes in which one attribute (or a group of attributes) determines the value of another attribute in the same table
Functional Dependency
Identification
The attribute (or attributes) that we use as the starting point (the varibale on the left side of the equation) is called a
Determinant
Identification
- A process of analyzing a relation to ensure that it is well-formed
- Involves decomposing relations with anomalies to produce smaller, well-structured relations
- When we apply this to a relation, rows can be inserted, deleted, or modified without creating anomalies
Normalization
Define
1NF
Remove multivalued attributes
Define
2NF
Remove partial dependencies
Define
3NF
Remove transitive dependencies
Identification
Candidate key
Composite key
Primary key
Surrogate key
Unique keys
Dan’s Topology of Db keys
Foreign key
Non-unique
Enumeration
Unique keys
Candidate key
Composite key
Primary key
Surrogate key
Non-unique key
Foreign key
Enumeration
SQL is comprised of
DDL
DML
DCL
Enumeration
DDL
DML
DCL
Data Definition Language
Data Manipulation Language
Data Control Language
Identification
Used to define and manage data structures
Data Definition Language
Identification
- Data definition and updating
- Data retrieval
Data Manipulation Language
Identification
For creating user accounts, managing permissions etc.
Data Control Language
Identification
used to specify that when a row is deleted from the parent table, all rows in the child table that reference the deleted row should also be deleted
ON DELETE CASCADE
Identification
used to specify that when a row is updated from the parent table, all rows in the child table that reference the updated row should also be updated
ON UPDATE CASCADE
Which normalization phase is being described?
every non-key attribute is fully functionally dependent on the entire primary key not just part of it
2NF
Which normalization phase is being described?
Every value is atomic
1NF
Which normalization phase is being described?
no transitive dependencies
transitive dependencies = functional dependencies on non-primary-key attributes
3NF
Identification
is a type of dependency that occurs when a non-key attribute depends on another non-key attribute
Transitive Dependency
Identification
a type of dependency that occurs when a non-key attribute depends on only one part of a composite primary key
Partial Dependency
Identification
- are used to specify rules for the data in a table
- are used to limit the type of data that can go into a table
Constraints
Identification
this constraint specifies that the constrained columns’ values must uniquely identify each row
Primary Key (Pk) constraint
Identification
this constraint ensures that only valid data can be inserted into the related column, as the value must exist in the referenced table
Foreign Key (Fk) constraint
Sql Syntax
Creating Table with PK constraints
CREATE TABLE Employee ( empID Integer NOT NULL, empName Char(25) NOT NULL, CONSTRAINT PRIMARY KEY (empID) );
Sql Syntax
Creating Table with PK and FK constraints
CREATE TABLE EmployeeSkill (
empID Integer NOT NULL,
skillId Integer NOT NULL,
skilllevel Integer NULL,CONSTRAINT PRIMARY KEY (empId, skillId),CONSTRAINT FOREIGN KEY (empID) REFERENCES employee(empID),CONSTRAINT FOREIGN KEY (skillId) REFERENCES Skill (skillid)
);
Sql Syntax
Create Table with update and delete cascades
CREATE TABLE Employeeskill (
empID integer NOT NULL,
skillId integer NOT NULL,
skilllevel integer NULL,CONSTRAINT PRIMARY KEY (empId, skillId),CONSTRAINT FOREIGN KEY (empId) REFERENCES employee (empId) ON DELETE CASCADE,CONSTRAINT FOREIGN KEY (skillId) REFERENCES Skill (skillId) ON UPDATE CASCADE
);
Identification
the same information may entered several times
Redundancy
Identification
each row may contain more than one business concept
Multiple themes
Identification
stores information in tables
Each business concept is stored in its own table
Relational Database
Uniqueness of keys
Data value is unique for each row
Consequently, the key will uniquely identify a row
Unique Key
Uniqueness of keys
Data value may be shared among several rows
Consequently, they key will identify a set of rows
Non-unique key
Identification
adds, deletes, modifies, renames the attributes of the relation
ALTER
Identification
modifies the values of the records in the relations
UPDATE
