Quiz 1

Question 1

What is a database?

Answer 1

• Collection of files
• Predefined structure
• Optimized for efficient retrieval

Question 2

How are databases different from data files?

Answer 2

• Structure for efficient retrieval
• Consistency: no contradictions
• Computer crash: database handles it
• Security
• Concurrency

Question 3

DBMS

Answer 3

Collection of programs that allow user to create, maintain, and query a database.

Question 4

What is the new philosophy regarding data and programs?

Answer 4

All about the data. Data is persistent, more important than the programs that access it.

Question 5

Four steps for building and using a relational databases

Answer 5

1. Design the schema: database design
2. Create the schema with DDL (data design language)
3. Load the database with initial data
4. Query and updates

Question 6

Example SQL for creation of schema

Answer 6

CREATE TABLE STUDENT(

NAME char(30)

SID Int NOT NULL

GPA float  );

Question 7

Define a query

Answer 7

In a high level language, NOT an algorithm.

• Easy to formulate
• Just because a query looks easy does not mean efficient retrieval

Question 8

Difference between DDL and DML

Answer 8

DDL (Data design language): This handles the design of the database.

DML (Data Manipulation language): For queries and updates.

• SQL can be used for both of these

Question 9

List names and GPA of students greater than 32 using SQL

Answer 9

SELECT name,gpa FROM Student WHERE gpa > 3.2

Question 10

Define the result of a query

Answer 10

• The result is a TABLE (or a view)
• It is closed mathematically

Question 11

What are the three theoretical bases of SQL?

Answer 11

• Set theory
• Relational algebra
• Predicate calculus

Question 12

Define the relational model

Answer 12

• Model that stores all its data in interconnected tables.
• High level query language
• Theoretical basis consisting of relational algebra and set theory
• Basic construct is a TABLE (relation)

Question 13

Define rows and columns in relational database design.

Answer 13

Rows: Tuple

Columns: Attributes

• GPA would be an attribute NAME
• 3.75 would be an attribute VALUE
• Each attribute has a type, NULL being special

Question 14

What two values can there be for NULL in relational database design?

Answer 14

Unknown and undefined

Question 15

True or false: Someone with a NULL GPA would still be listed in the following queries: SELECT * FROM Students WHERE GPA Not 3.5 SELECT * FROM Students WHERE GPA > 3.5 SELECT * FROM Students WHERE GPA

Answer 15

False

Question 16

Define schema

Answer 16

Structural description of table

Question 17

Define instance

Answer 17

Actual contents of table at that time

Question 18

Does ordering of the tuples matter?

Answer 18

No, and that goes back to set theory. {1,2,3,4} = {2,4,3,1}

Question 19

Does ordering of the columns matter?

Answer 19

Yes, if attribute names are not specified. The following statement would not work without ordering of columns. INSERT INTO Students VALUES (‘Smith’, 17, 3.8, 1)

Question 20

True or false: Every tuple MUST be unique.

Answer 20

True

Question 21

Define a “Key”

Answer 21

A set of attributes whose value is unique in each tuple

Question 22

What are the different types of keys?

Answer 22

Primary key: Only one candidate key can become a primary key. Underlined in schema.

Candidate key: A set of attributes that qualify as a unique key in a database. Each one can qualify as a primary key.

Question 23

What are set members in relational database design?

Answer 23

All of the tuples in the table

Question 24

A union B

Answer 24

Everything in A, B, and the part that intersects A and B.

Question 25

A intersect B

Answer 25

Only the part that intersects A and B.

Question 26

Compliment of A

Answer 26

EVERYTHING outside of A (drawn with a box)

Question 27

A - B

Answer 27

Everything in A that does not intersect with B.

Question 28

A X B

Answer 28

{1,2,3} x {a,b} = {(1,a), (1,b), (1,c), (2,a), (2,b), (2,c), (3,a), (3,b), (3,c)}

Question 29

What are the operators in relational algebra?

Answer 29

Union, intersect, cross, subtraction, select (sigma) and project (pi)

Question 30

Relational algebra query to list employees from department number 4.

Answer 30

σ\_dno=4 (Employee)

Question 31

Relational algebra query to list employees with salary \> 30000

Answer 31

σ\_salary\>30000 (Employee)

Question 32

Relational algebra query to list employees from department 4 and salary \> 25000 or salary \< 40000

Answer 32

σ\_dno=4 ^ salary\>25000 v salary\<40000(Employee)

Question 33

Relational algebra query to list employees fname, lname, and salary

Answer 33

π\_fname,lname,salary(Employee)

Question 34

Explain how duplicates are handled in relational algebra versus SQL

Answer 34

Relational Algebra: Duplicates are NOT included in output SQL: Equivalent query WOULD contain duplicatesi in output

Question 35

Relational algebra query to list last name and salary of employees in department 5.

Answer 35

π\_lname,salary(σ\_dno=5 (Employee))

Question 36

Relational algebra query to list the last name and salary of employees in department 5 who earn \> 30000

Answer 36

π\_lname,salary(σ\_dno=5 ^ salary\>30000 (Employee))

Question 37

Rename operator

Answer 37

ρ: Renames an output table. ρ\_firstName(π\_fname(EMPLOYEE))

Question 38

When can you use the union, intersects, or subtraction operator on tables?

Answer 38

When the attributes in both tables have identical types: Same attribute domains (i.e. numbers, names, etc.)

Question 39

Student: Adam, Jon, Mary Instructors: Adam, Dona Student union Instructors

Answer 39

Adam, Jon, Mary, Dona

Question 40

Student: Adam, Jon, Mary Instructors: Adam, Dona Student intersects Instructors

Answer 40

Adam

Question 41

Student: Adam, Jon, Mary Instructors: Adam, Dona Student - Instructors

Answer 41

Jon, Mary

Question 42

Student: Adam, Jon, Mary Instructors: Adam, Dona Instructors - Student

Answer 42

Dona

Question 43

Relational algebra query to list SSN of all employees who either work in department 5 or supervise an employee who works in department 5.

Answer 43

π\_ssn(σ\_dno=5(EMPLOYEE)) U π\_super\_ssn(σ\_dno=5(EMPLOYEE))