Attribute, domain, and tuple

Entity, relation name, and domain

Table name, primary key, and entity

Attribute, relation name, and primary key

A virtual table is a relation created as the result of data manipulation; it exists only in computer memory, and is not a permanent part of the database.

A virtual table is a relation stored in the database; it is used when multiple users access the same relation in a database.

A virtual table is a relation derived from the database data dictionary; it contains metadata about a base relation. A virtual table is a relation derived from the database? data dictionary; it contains metadata about a base relation.

A virtual table is a relation that consists of primary and foreign keys for a particular set of relations in a database.

A set of normalized relations

A reviewed entity-relationship (ER) model

An entity-relationship (ER) model with no redundant data

A set of denormalized relations

Logical

Physical

Application

Conceptual

SELECT * FROM Dept1_Parts EXCEPT(SELECT PartJD FROM Dept2_Parts);

SELECT * FROM Dept1_Parts MINUS (SELECT Part_ID FROM Dept2_Parts);

SELECT * FROM Dept1_Parts DIFFERENCE (SELECT Part_ID FROM Dept2_Parts);

SELECT * FROM Dept1_Parts WHERE Part_ID NOT IN (SELECT Part_ID FROM Dept2_Parts);

CREATE TABLE RESIDENT ( R_ID INTEGER NULL PRIMARY KEY, Room_Num FLOAT, Res_Name VARCHAR, Building_ID INTEGER NULL, FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));

CREATE TABLE RESIDENT ( R_ID INTEGER NOT NULL PRIMARY KEY, Room_Num BINARY, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL, FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));

CREATE TABLE RESIDENT ( R_ID INTEGER NOT NULL PRIMARY KEY, Room_Num INTEGER, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL);

CREATE TABLE RESIDENT ( R_ID INTEGER NOT NULL PRIMARY KEY, Room_Num INTEGER, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL, FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));

Update

Transaction

Encapsulation

Operational group

Information Engineering (IE) data model

Corporate data model

Database requirements model

ERD model

SELECT * FROM Customers WHERE Satisfaction_Rate <= 80 OR Satisfaction_Rate >= 90;

SELECT * FROM Customers WHERE Satisfaction_Rate IN (80 AND 90);

SELECT *FROM Customers WHERE Satisfaction_Rate >= 80 AND Satisfaction_Rate <= 89;

SELECT * FROM Customers WHERE Satisfaction_Rate BETWEEN (80, 90);

CREATE

CREATE TABLE

CREATE DOMAIN

CREATE SCHEMA

If a parent key is deleted, any child keys referenced by the parent key are automatically deleted.

If a parent key is deleted, no test is made for referential integrity.

If any child key references a parent key, the record containing the parent key cannot be deleted.

If a parent key is deleted, all child keys are automatically set to a specified value. C

SELECT * FROM Customers, Employees WHERE Sales_Rep_No = Cust_No;

SELECT Cust_No, Cust_Name, Emp_Name, Emp_Loc FROM Customers, Employees WHERE Customers.Sales_Rep_No = Employees.Sales_Rep_No;

SELECT * FROM Customers, Employees WHERE Customers.Sales_Rep_No = Employees.Sales_Rep_No;

A normalized set of data applicable to a particular relation

A combination of attributes for a relation

A definition of permissible values for one or more attributes

A set of permissible values for one or more relations

0002, James, Smith

0002, James, Smith, 10-25-76

First_Name, James, Last_Name, Smith

Emp_ID, 0002, First_Name, James, Last_Name, Smith

Union and join

Selection and projection

Intersection and difference

Cartesian product and intersection

Provides support for a data manipulation language

Allows users to store data in a distributed data repository

Provides support for data formatting language commands

Translates procedural commands into non-procedural commands

6

20

4

25

A group of attributes

A set of permissible tuple values

A collection of related data items

A set of permissible attribute values D

25

5

4

20

Implement a tabular structure.

Gather files in a distributed repository.

Use a hierarchical database file system.

Tightly couple database structure to database application programs.

BUILDING(Building_ID, Bldg_Name, Location, Room_Count) Primary Key Building_ID RESIDENT(R_ID, Room_Num, Res_Name, Building_ID) Primary Key R_ID

BUILDING(Building_ID, Bldg_Name, Location, Room_Count) Primary Key BUILDING RESIDENT(R_ID, Room_Num, Res_Name, Building_ID) Primary Key RESIDENT

BUILDING(Building_ID, Bldg_Name, Location, Room_Count) Primary Key BUILDING Foreign Key BUILDING(Building_ID) references RESIDENT (Building_ID) RESIDENT(R_ID, Room_Num, Res_Name, Building_ID) Primary Key RESIDENT

BUILDING(Building_ID, Bldg_Name, Location, Room_Count) Primary Key Building_ID RESIDENT(R_ID, Room_Num, Res_Name, Building_ID) Primary Key R_ID Foreign Key Building_ID references BUILDING(Building_ID)

1:1

1:N

N:1

M:N

Management of data redundancy, increased data integrity, increased data dependence, and increased application program flexibility.

Consistency of data, adherence to standards, managed concurrency, and increased software complexity.

Increased data access, increased data backup and recovery, data sharing, and consistency of data.

Increased data security, increased data integrity, increased data independence, and decreased data separation.

The number of domains changes.

The attributes in the domain change.

The cardinality of the relation is fixed but the degree varies.

The degree of the relation is fixed but the cardinality varies.

An update anomaly and an insertion anomaly would occur.

An insertion anomaly would occur.

A deletion anomaly would occur.

A deletion anomaly and an update anomaly would occur.

One-to-many and recursive

Complex, recursive, and many-to-many

Redundant, recursive, and one-to-many

One-to-many and one-to-one

Backups

• User views

Encryption

Integrity controls

1NF

1NF and 2NF

1NF, 2NF and 3NF

1NF, 2NF, 3NF and BCNF

S_Date and E_Date

ProjJD

ltem_Num and E_Date

Proj_ID and Item_Num

A composite key is a primary key that consists of the first two attributes of a relation.

A composite key is a primary or foreign key defined by its parent keys.

A composite key is a foreign key that consists of the same attributes as the primary key from a related table.

A composite key is a primary or foreign key that consists of two or more attributes of a relation.

Logical

Physical

Conceptual

Implementation

UPDATE Recreation SET Activity, Activity_Fee (Tennis,100) WHERE Student_ID = 1003;

UPDATE TABLE Recreation ALTER COLUMN ACTIVITY SET ACTIVITY = Tennis', Activity_Fee = 100 WHERE Student__ID = 1003 AND Activity = wimming?AND Activity = ?wimming?

UPDATE Recreation SET Activity = Tennis', Activity_Fee = 100 WHERE Student_ID = 1003 AND Activity = 'Swimming';

DELETE Activity FROM Recreation WHERE Student_ID = 1003; INSERT INTO Recreation VALUES (1003, Tennis', 100);

Lock

Deadlock

Time stamp

Transaction

Repetition of data, application program flexibility, and data centralization

Incompatibility of files, tabular data structures, and data dependence

Separation of data, repetition of data, and data independence

Application program inflexibility, data dependence, and separation of data

CREATE TABLE BUILDING ( Building_ID NOT NULL PRIMARY KEY, Bldg_Name, Location, Room_Count);

CREATE TABLE BUILDING ( Building_ID NOT NULL PRIMARY KEY, Bldg_Name, Location, Room_Count, FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));

CREATE TABLE BUILDING (Building_ID INTEGER NOT NULL PRIMARY KEY, Bldg_Name VARCHAR (20), Location VARCHAR (50),

CREATE TABLE BUILDING (Building_ID INTEGER NOT NULL PRIMARY KEY, Bldg_Name VARCHAR (20), Location VARCHAR (50), Room_Count INTEGER CHECK (Room_Count > Room_Count INTEGER CHECK ( -1 And Room_Count < 601)); Room_Count > -1 Or Room_Count < 601));

1,2,3,4

3,4,1,2

4,3,1,2

4,2,3,1

Fat client

Enterprise client

Thin client

Terminal client

An update anomaly would occur.

An insertion anomaly would occur.

A deletion anomaly would occur.

Both an insertion anomaly and a deletion anomaly would occur.

A relation

Data about data

Data stored in a table column

• An item about which information is stored

Rollback

Deadlock

Dirty read

No problem has occurred.

Degree of a relation

Cardinality of a relation

Primary key of a relation

Determinant of a relation

Entity integrity

Necessary data

Referential integrity

Referential foreign integrity

SELECT Sales_Rep_No(108) FROM Orders;

INSERT INTO Orders VALUES(Sales_Rep_No = 108) WHERE Sales_Rep_No = NULL;

SELECT'FROM Orders WHERE Sales_Rep_No = 108;

SELECT'FROM Orders WHERE Sales_Rep_No = ?08?

SELECT Course_Code FROM Registration;

SELECT * FROM Registration WHERE Registration_ID = 1003 AND Registration_ID = 1005;

SELECT * FROM Registration WHERE Course_Code = A4343';

SELECT Registration_ID, Student_ID, First_Name, Last_Name

CREATE VIEW Emp_Dept AS SELECT Last_Name, First_Name, Dept_ID FROM Employee;

UPDATE VIEW Emp_Dept AS SELECT * FROM Employee;

UPDATE VIEW Emp_Dept AS SELECT Last_Name, First_Name, Dept_ID FROM Employee;

CREATE VIEW Emp_Dept AS SELECT * FROM Employee WHERE ID = 0001 AND ID = 0002 AND ID = 0003 AND ID = 0004;

Data Markup Language

Data Control Language

Data Formatting Language

Data Manipulation Language

Entity integrity

Domain integrity

Domain constraints

Referential integrity

DELETE * FROM Customers WHERE Sales_Office = New York;

DELETE FROM Customers WHERE Sales_Office = ew York?WHERE Sales_Office = ?ew York?

DELETE * FROM Customers WHERE Sales_Office = ew York?WHERE Sales_Office = ?ew York?

DELETE FROM Customers WHERE Sales_Office NOT LIKE ew York? WHERESales_Office NOT LIKE ?ew York?

A system catalog containing user data

An area of the database that is directly accessible by the user

Data that is stored in tables and is only accessible by the DBMS

Metadata that is stored in tables and is only accessible by the DBMS

It contains a query language for retrieving data.

It can be used only to manipulate data through a SQL interface.

The user is not required to know how the underlying data structures are implemented.

It requires that the user know how the underlying data structures are implemented.

Theft

Privacy

Availability

Confidentiality

Union

Selection

Projection

Difference

Second normal form

First normal form

Boyce-Codd normal form

Third normal form

REVOKE INSERT, UPDATE, DELETE ON Employees FROM Group_2;

REVOKE UPDATE FOR Employees FROM Group_2;

REVOKE INSERT, UPDATE, DELETE ON Employees FOR Group_2;

REVOKE UPDATE, DELETE FOR Employees FROM Group_2;

1:1

Transitive

Functional

Partial functional

INSERT INTO Atlanta_Customers VALUES( SELECT * FROM Customer s WHERE Sales_Office = Atlanta

INSERT INTO Atlanta_Customers SELECT * FROM Customers WHERE Sales_Office = Atlanta

INSERT INTO Atlanta_Customers SELECT Cust_No, Cust_Name, Satisfaction_Rate, Sales_Rep_No FROM Customers WHERE Sales_Office = Atlanta

INSERT INTO Atlanta_Customers SELECT Cust_No, Cust_Name, Sales_Office, Sales_Rep_No FROM Customers WHERE Sales_Office = Atlanta

SELECT * FROM
Dept1_Parts
EXCEPT(SELECT
PartJD FROM
Dept2_Parts);

SELECT * FROM Dept1_Parts MINUS
(SELECT Part_ID
FROM Dept2_Parts);

SELECT * FROM Dept1_Parts
DIFFERENCE
(SELECT Part_ID
FROM Dept2_Parts);

SELECT * FROM Dept1_Parts WHERE
Part_ID NOT IN
(SELECT Part_ID FROM
Dept2_Parts);

CREATE TABLE RESIDENT (
R_ID INTEGER NULL PRIMARY KEY,
Room_NumFLOAT,
Res_NameVARCHAR,
Building_IDINTEGER NULL,
FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));

CREATE TABLE RESIDENT (
R_ID INTEGER NOT NULL PRIMARY KEY,
Room_NumBINARY,
Res_NameVARCHAR (20),
Building_IDINTEGER NOT NULL,
FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));

CREATE TABLE RESIDENT (
R_ID INTEGER NOT NULL PRIMARY KEY,
Room_NumINTEGER,
Res_NameVARCHAR (20),
Building_IDINTEGER NOT NULL);

CREATE TABLE RESIDENT (
R_ID INTEGER NOT NULL PRIMARY KEY,
Room_NumINTEGER,
Res_NameVARCHAR (20),
Building_IDINTEGER NOT NULL,
FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));

The user accesses a database server using a terminal.

The user interface, data-processing logic, database access and data validation functions are performed on a mainframe server.

The user interface and data validation functions are performed by the client whereas the data- processing logic is performed on a server.

The user interface and data-processing logic are performed by the client whereas the server handles database access and data validation functions.

Activity_Fee is a determinant of Activity.

Activity_Fee is partially dependent on the key.

The table contains a transitive dependency.

Activity_Fee is a determinant of Activity and Student_ID.

STUDENT(
Student_Number: integer NOT NULL
Name: variable length character string length 20 NOT NULL) Primary Key Student_Number
CLASS(
Class_Num: integer NOT NULL
Class_Name: integer NOT NULL)
Primary Key Class_Num

STUDENT(
Student_Number: integer NOT NULL
Name: variable length character string length 20 NOT NULL) Primary Key Student_Number
CLASS(
Class_Num: integer NOT NULL
Class_Name: integer NOT NULL)
Primary Key Class_Num
Foreign Key Class_Num References STUDENT

STUDENT(
Student_Number: integer NOT NULL
Name: variable length character string length 20 NOT NULL) Primary Key Student_Number
STU_CLASS(
Student_Number: integer NOT NULL
Class_Num: integer NOT NULL)
Primary Key Student_Number
CLASS(
Class_Num: integer NOT NULL
Class_Name: integer NOT NULL)
Primary Key Class_Num

STUDENT(
Student_Number: integer NOT NULL
Name: variable length character string length 20 NOT NULL) Primary Key Student_Number
STU_CLASS(
Student_Number: integer NOT NULL
Class_Num: integer NOT NULL)
Primary Key Student_Number, Class_Num
CLASS(
Class_Num: integer NOT NULL
Class_Name: integer NOT NULL)
Primary Key Class_Num

Third normal form

Second normal form

No normal form

First normal form

A recursive relationship

A many-to-many relationship

A one-to-one relationship

A one-to-many relationship

SELECT * FROM
Registration WHERE
Course_Code = #

SELECT * FROM
Registration WHERE
Course_Code LIKE _

SELECT* FROM
Registration WHERE
Course_Code LIKE %

SELECT * FROM
Registration WHERE
Course Code = %

CREATE TABLE BUILDING (
Building_ID INTEGER NOT NULL PRIMARY KEY,
Bldg_Name VARCHAR (20),
Location VARCHAR (20),
Room_Count INTEGER );
CREATE TABLE RESIDENT (
R_ID NOT NULL PRIMARY KEY,
Room_Num INTEGER,
Res_Name VARCHAR (20),
Building_ID INTEGER NOT NULL,
FOREIGN KEY Building_ID REFERENCES RESIDENT (Building_ID) ON DELETE NO CHECK);

CREATE TABLE BUILDING (
Building_ID INTEGER NOT NULL PRIMARY KEY,
Bldg_Name VARCHAR (20),
Location VARCHAR (20),
Room_Count INTEGER );
CREATE TABLE RESIDENT (
R_ID NOT NULL PRIMARY KEY,
Room_Num INTEGER,
Res_Name VARCHAR (20),
Building_ID INTEGER NOT NULL,
FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID) ON DELETE NO CHECK
ON UPDATE CASCADE);

Data Query Language

Database Entity Language

Data Definition Language

Data Manipulation Language

It is the primary key for an entity.

It uniquely identifies every instance of an entity.

One or more keys are joined together to form a composite key.

One or more keys may be used to form a primary key.

A Web browser is used as the application server.

The application logic is centralized on a dedicated server.

A thick client is used to perform business application logic functions locally.

Database application logic and database functionality are integrated and reside on a common server.

Entity

Domain

Primary key

Attribute group

SELECT * FROM Customers
WHERE Satisfaction_Rate > 80
OR Sales_Office = Atlanta

SELECT * FROM Customers
WHERE Satisfaction_Rate <= 80
AND Sales_Office = Atlanta

SELECT * FROM Customers
WHERE Satisfaction_Rate >= 80;

SELECT * FROM Customers
WHERE Satisfaction_Rate >= 80
AND NOT Sales Office = Atlanta

Employee.Dept_ID

Dept_Mngr

Dept_Name

Department.Dept_ID

Two-tier using thin client

Three-tier using fat client

Three-tier using thin client

Centralized mainframe with terminal client

A DBMS provides the ability to control data access and limit the number of users at one time.

A DBMS provides the ability to maintain databases while providing increased security for the database.

A DBMS allows users to access the database while allowing the database administrator to define views particular to individual sets of users.

A DBMS provides the ability to define, create and maintain databases while providing controlled access to databases.

SELECT *
FROM Orders
WHERE Order_Date LIKE _02
ORDER BY Amount;

SELECT (Order_Date, Amount)
FROM Orders
WHERE Order_Date LIKE %02
ORDER BY Amount;

SELECT *
FROM Orders
WHERE Order_Date LIKE 02
ORDER BY OrderNo;

SELECT *
FROM Orders
WHERE Order_Date LIKE %02
ORDER BY Amount;

SELECT Activity FROM Recreation;

SELECT DISTINCT Activity FROM Recreation;

SELECT Activity FROM Recreation
WHERE NOT LIKE Activity;

SELECT Activity FROM Recreation
WHERE DISTINCT Activity;

Thin client

Fat client

Database server

Application server

Entity

Matrix

Relation

Data dictionary

Locking

Time stamps

Optimistic

Serialization

Data files are owned and maintained by the users.

Database administration is simplified.

A standard method can be used to access the database.

Data is decentralized.

Union

Intersection

Natural join

Cartesian product

prime key.

indirect key.

parent key.

composite key.

Durability

Consistency

Isolation

Atomicity

File locking

User authorization

Parity checks

Integrity controls

INSERT INTO Employee VALUES(0005, Tim, Bogart, 03-15-77);

INSERT INTOEmployee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0004, Tim, Bogart, 03-15-77);

INSERT INTOEmployee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0005, Tim, Bogart, 03-05-77);

The ordering of attributes in a table is significant.

An attribute contains values from multiple domains.

An attribute name can be used only once per table.

An attribute name can be used only once in a relational database system with multiple tables.

Entity integrity

Domain constraint

Referential integrity

Enterprise constraint

Cust_Name is the determinant.

Cust_Name is transitively dependent on Cust_ID.

Cust_ID is transitively dependent on Cust_Name.

Cust_Name is functionally dependent on Cust_ID.

SELECT *
FROM Employee;

SELECT ID,Last_Name
FROM Employee;
WHERE ID = 0004;

SELECT *
FROM Employee
WHERE Dept_ID = 022;

Flat-file database

Relational database

Distributed database

Object-oriented database

A primary key should be a value that may be null and may change over time.

A primary key should be a value that is not null and will never change.

A primary key should consist of meaningful data and a value that can be changed if needed.

A primary key should not consist of meaningful data and a value that can be changed if needed.

Union

Difference

Projection

Intersection

Create intermediate entities.

Create a logical data model.

Resolve many-to-many relationships.

Identify and resolve complex relationships.

There is no relationship between changes inSTUDENT(Student_Number) and HOUSING(Student_Number).

WhenSTUDENT(Student_Number) is changed or deleted, this modification or deletion will automatically be reflected in HOUSING (Student_Number).

Modifications toHOUSING(Student_Number) are automatically reflected in changes to STUDENT(Student_Number), but deletions are not permitted.

Modifications toSTUDENT(Student_Number) are automatically reflected in changes to HOUSING(Student_Number). For a deletion to occur from STUDENT(Student_Number), it must first occur in HOUSING(Student_Number).

Boyce-Codd normal form

First normal form

Second normal form

Third normal form

SELECT
FROM Dept1_Parts
AND
(SELECT
FROM Dept2_Parts);

SELECT
FROM Dept1_Parts
INTERSECTION
(SELECT
FROM Dept2_Parts);

SELECT *
FROM Dept1_Parts
WHERE Dept1_Parts.Part_ID = Dept2_Parts.Part_ID;

The database designer tests the software because he or she is able to make necessary changes to the underlying code for the software.

A user who has no knowledge of thesoftwares underlying code tests the software.

Someone other than the database designer tests the software. This person has no access to the underlying code and attempts to use the software only in ways not considered by the software designers.

A person tests the software and submits suggestions to the software’s underlying code. This person is someone other than the database designer, but has access to thesoftwares underlying code.

SELECT *
FROM Dept1_Parts
EXCEPT
(SELECT Part_ID
FROM Dept2_Parts);

SELECT *
FROM Dept1_Parts
MINUS
(SELECT Part_ID
FROM Dept2_Parts);

SELECT *
FROM Dept1_Parts
DIFFERENCE
(SELECT Part_ID
FROM Dept2_Parts);

Normalization

Denormalization

Referential integrity

Domain constraints

Data Control Language

Data Definition Language

Data Formatting Language

Data Manipulation Language

m:m

m:n

1:1

1:n

If a parent key is deleted, any child keys referenced by the parent key are automatically deleted.

If any child key references a parent key, the record containing the parent key cannot be deleted.

If a parent key isdeleted, all child keys are automatically set to a specified value.

If a parent key is deleted, no test is made for referential integrity.

Only an update anomaly would occur.

An insertion anomaly would occur.

A deletion anomaly would occur.

An update anomaly and a deletion anomaly would occur.

GRANT UPDATE ON Project TO Rubio, Doe;
GRANT SELECT ON Project TO Temp;

GRANT ALL PRIVILEGES ON Project TO Rubio, Doe;
GRANT UPDATE ON Project TO Temp;

GRANT SELECT ON Project
WHERE Manager = ‘Rubio’;
GRANT SELECT ON Project
WHERE Manager = ‘Doe’;

GRANT UPDATE ON Project
WHERE Manager = ‘Rubio’;
GRANT UPDATE ON Project
WHERE Manager = ‘Doe’;
GRANT SELECT ON Project TO Temp;

UPDATESales_Rep_No
IN Orders
SET(Sales_Rep_No = 110
WHERE Sales_Rep_No = 108);

UPDATE Orders
SET Sales_Rep_No = 110
WHERE Sales_Rep_No = 108;

UPDATE Orders
SET Sales_Rep_No = 110;

UPDATE Orders
WHERE Sales_Rep_No = 108
SET Sales_Rep_No = 110;

the intersection of two relations.

a Cartesian product of two relations.

a restricted Cartesian product of two relations.

the Cartesian product of two union-compatible relations.

Concurrent transactions read data from the same data structure.

Concurrent transactions write data to different data structures.

Concurrent transactions read or write the same data structure.

Concurrent transactions read or write from different data structures.

Data that is stored as tables

Software that manipulates data

Data that is stored in a structured manner

Data recordsthat are stored sequentially in a file

No normal form

Second normal form

First normal form

Third normal form

Parallelism

Serialization

Database control

Concurrency control

Two records

Three records

Four records

Five records