CSE 3330 Databases Exam 1

What is a schema?

description of database

What is a primary key?

a designated candidate key. Guarantees each row in the table has a unique value.

What is a foreign key?

A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables

What is a candidate key?

A minimal superkey that uniquely identifies tuples. A subset of attributes that can be used to uniquely identify any record in a table.

T/F: Two relations can share the same schema

True

T/F: Data independence means that a program that uses a database does not need to change if the database changes

True

T/F: In a table of student records, it was observed that no two students have the same name. Thus, one can conclude that student name is a candidate key.

False

T/F: A declarative query specifies the precise sequence of steps a database must take to answer

False

Difference between procedural queries and declarative queries.

Procedural queries are navigational while declarative queries are non-navigational

T/F: One difference between a relation in Relational Algebra and a table in a SQL database is that the SQL table may contain duplicate rows

True

Briefly explain the three-level schema architecture

1. Physical/internal schema

2. Conceptual/logical schema

3. External schema/ external view

What is a physical schema/ internal view?

Describes the physical storage of data, such as file formats and indexes

What is conceptual scheme/logical view?

Logical view of the database, including what data is stored and relationship between data iterms

What is external schema/external view?

Represents the user's view of the database. Different users can have different views of the database

Consider the transaction below, which is meant to transfer $1,000 from Account A to Account B.Describe a scenario in which a hardware failure in the middle of transaction execution can leave thedatabase in an inconsistent state. BalA = getbal(AccountA)if (BalA>1000)BalB = getbal(AccountB)setbal(AccountA,BalA-1000)setbal(AccountB,BalB+1000)

If the hardware fails after setbal(AccountA,BalA-1000) is executed and the balance of AccountA isreduced, but before setbal(AccountB,BalB+1000) is executed, the database will be in an inconsis-ten state. Money has been taken from AccountA but not added to AccountB, so this money has"disappeared" from the database.

What is the difference between a database schema and a database state?

A database schema is a description of the data interface to the database while database instance is a database that conforms to a given schema

Describe the three-schema architecture. Why do we need mappings among schema levels? How do different schema definition languages support this architecture?

The three-schema architecture consists of the internal view/physical schema, logical view/conceptual schema, and external view/external schema. We need mappings among schema levels because it helps us flexible move in between and edit schemas without the risk of causes any modifications to other schemas in the architerture. The Data Definition Language (DDL) is used to define schemas at the conceptual and physical levels while the view definition language (VDL) is used to define schemas at the external level.

What is the difference between logical data independence and physical data independence?

The difference between logical data independence and physical data independence is that logical data independence is how flexibly a user can modify the conceptual schema without affecting other schemas while the physical data independence showcases how flexibly a user can modify the physical schema without affects other schemas in the architecture.

What is the difference between logical data independence and physical data independence? Which one is harder to achieve? Why?

Logical data independence is harder to achieve because changes to the conceptual schema can impact how the data is used by many different user views and applications. It requires more complex mappings and adjustments compared to physical independence, where only storage-related changes are made.

Why are tuples in a relation not ordered?

Mathematically, elements of a set have no order among them; hence, tuples in a relation do not have any particular order.

Why are duplicate tuples not allowed in a relation?

Duplicate tuples are not allowed in a relation because a relation represents a set of tuples, and sets do not contain duplicates. This ensures the integrity of the data, prevents redundancy, and simplifies the process of querying and managing data.

What is the difference between a key and a superkey?

Superkey: Can have extra attributes that can uniquely define a tuple

Key: Minimal superkey (no unnecessary attributes) but removing any tuple that would no longer make it a unique identifier

Define foreign key. What is this concept used for?

Links different relations based on common attributes.

Consider the following relationsforadatabasethatkeepstrackofstudent enrollment in courses and the books adopted for each course:

STUDENT(Ssn, Name, Major, Bdate) COURSE(Course#, Cname, Dept)ENROLL(Ssn, Course#, Quarter, Grade) BOOK_ADOPTION(Course#, Quarter, Book_isbn) TEXT(Book_isbn, Book_title, Publisher, Author)

Specify the foreign keys for this schema, stating any assumptions you make.

- SSN is foreign key for linking student and enroll

- Course # is for course to enroll and book_adopt

- book_isbn is to link book_adopt to text

What is the only attribute that can uniquely define a tuple?

Primary key

What are 4 properties of transactions ensured by the database management system

-Atomic

-Consistent

-Isolated

-Durable

What are the two-sublanguages that SQL is made up of:

- Data Manipulation Language (DML)

- Data Definition Language (DDL)

What does SELECT DML command do

statements pefrom queries

What does INSERT, UPDATE, DELETE DML command do

statements that modify instance of a table

What does CREATE, DROP DDL language do

statements modify database schema

What does GRANT, REVOKE DDL language commands do

Statements that enforce security model

Write the following SQL Query: Find the last names and hire dates of employees who make more than $100000

select LastName, HireDate

from Employee

where Salary > 100000

T/F: SQL is declarative

True

What statements is used to eliminate duplicates from the result of a query in SQL

Select distinct

T/F: SQL Queries may result in duplicates even if none of the input themselves are duplicates

True

Three-Valued Logic for SQL

True, False, NULL

SQL:

Given:

Sailors(sid:integer, sname: integer, rating:integer, age: real)

boats(bid:integer, bname:string, color:string)

reserves(sid:integer, bid:integer, day: date) ->primary key

1. Find names and ages of all sailors

select Sailors.sname, Sailors.age

from Sailors

SQL:

Given:

Sailors(sid:integer, sname: integer, rating:integer, age: real)

boats(bid:integer, bname:string, color:string)

reserves(sid:integer, bid:integer, day: date) ->primary key

2. Find names, ages, and rating of all sailors whose rating is above 7

select Sailors.sname, Sailors.age, Sailors.rating

from Sailors

where Sailors.rating > 7

SQL:

Given:

Sailors(sid:integer, sname: integer, rating:integer, age: real)

boats(bid:integer, bname:string, color:string)

reserves(sid:integer, bid:integer, day: date) ->primary key

3. Find the sids and name of sailors who reserved boat 103 (boat id)

select Sailors.sid, Sailors.name

from Sailors, Reserves

where Sailors.sid=Reserves.sid and Reserves.bid = '103'

SQL:

Given:

Sailors(sid:integer, sname: integer, rating:integer, age: real)

boats(bid:integer, bname:string, color:string)

reserves(sid:integer, bid:integer, day: date) ->primary key

5. Find sailor ids and names of sailors who have reserved a red boat. Make sure your result has no duplicates

select distinct Sailors.sid, Sailors.names

from Sailors, Reserves, Boat

where Sailors.side=Reserves.sid and Reserves.bid=Boat.bit and Boat.color='red'

SQL:

Given:

Sailors(sid:integer, sname: integer, rating:integer, age: real)

boats(bid:integer, bname:string, color:string)

reserves(sid:integer, bid:integer, day: date) ->primary key

6. Find sids of sailors who reserved a red boat. No duplicates

select distinct Reserves.sid

from Reserves, Boat

where Reserves.bid=Boat.bit Boat.color = ‘red’

SQL:

Given:

Sailors(sid:integer, sname: integer, rating:integer, age: real)

boats(bid:integer, bname:string, color:string)

reserves(sid:integer, bid:integer, day: date) ->primary key

6. Find the sids of sailors who did not reserve any boats

select S.sid

from sailors S

except

select R.sid

from Reserve R

SQL:

Given:

Sailors(sid:integer, sname: integer, rating:integer, age: real)

boats(bid:integer, bname:string, color:string)

reserves(sid:integer, bid:integer, day: date) ->primary key

7. Find sids of sailors who have a rating of 10 or have reserved a boat id of 103

Select sailors.sid

From Sailors, reserves

Where sailors.sid = reserves.sid And (sailors.age > 10 Or reseves.bid = 103)

What is a referential integrity?

A tuple with a non-null value for a foreign key that does not match the primary key value of a tuple in referenced relation is not allowed.