Exam Flashcards
What is a Data Model?
a. A model describing data.
b. A collection of concepts describing data.
c. A description of a collection of data.
d. A collection of schemas describing data.
b. A collection of concepts describing data.
Explanation: A Data Model is a collection of multiple concepts that describe the data. A single concept does not explain all tables, thus it needs to be a collection of multiple.
Owner entity is associated with one or more weak entities, but each weak entity
has a single owner.This relationship set is called the ________ set of the weak entity
set.
a. Independent Relationship
b. Total Participation
c. Identifying Relationship
d. None of the above
Identifying Relationship
Explanation: An identifying relationship is a relationship between two entities in
which an instance of a weak entity is identified through its association with an owner
entity, which means the weak entity is based on the owner entity for its identity and
cannot exist without it.
Note: Lets say we have an entity(table) Person, which have an attribute(ID). If there would be another entity, such as a child we could note the child entity as a weak entity as the child can not be identified without it’s parent entity(Person). Therefore we need an IDENTIFYING relationship to identify to which Person this child belongs.
Given table : | StudentID | CourseID | StartDate | EndDate |
Which aggregate function will NOT generate an error on the StartDate(datatype :
date) column?
a. COUNT(StartDate)
b. AVG(StartDate)
c. SUM(StartDate)
d. All of the above
d. All of the above
Explanation: All of the functions that are mentioned in the options run and give you
a value for StartDate with data type date.
Modify the ‘Student’ table to add a primary key on the StudentID column. We
assume that the table is currently empty.
Given table : | StudentID | CourseID | StartDate | EndDate |
Which statement is correct in order to perform this task?
a. ALTER TABLE Student
ADD PRIMARY KEY StudentID;
b. MODIFY TABLE Student
ADD CONSTRAINT PRIMARY KEY (StudentID);
c. ALTER TABLE Student
ADD CONSTRAINT StudentID_PK PRIMARY KEY (StudentID);
d. MODIFY TABLE Student
ADD CONSTRAINT StudentID_PK PRIMARY KEY StudentID;
c. ALTER TABLE Student
ADD CONSTRAINT StudentID_PK PRIMARY KEY (StudentID);
Explanation: This question is a syntax question where the only right option is to use
the ALTER command and remembering to use the ADD CONSTRAINT
Note: ALTER TABLE is used for altering a table means to change column name, size, drop column. CHANGE COLUMN and MODIFY COLUMN commands cannot be used without help of ALTER TABLE command.
Given these tables:
Sailor (SID, NAME, RATING, AGE)
Boat (BID, NAME, SIZE, COLOR)
WorksOn (SID, BID)
What is the result of this expression: (∀B | ¬ (B ∈Boat)) ?
a. This query will give a list of all fields from Boat table
b. This query has infinite results.
c. This query will only give a list of Boat ID
d. This query will evaluate to NULL
b. This query has infinite results.
Explanation: The query asks for all B such that B is not in Boats. That is infinite.
Note: (∀B | ¬ (B ∈Boat)) = (For all boats | Not (Boats in Boat))
Given these tables: Sailor (SID, NAME, RATING, AGE) Boat (BID, NAME, SIZE, COLOR) WorksOn (SID, BID) What will this expression give:
{P / ∃B ∈ Boat ( B.SIZE >7 ∧ B.COLOR = ’Blue’ ∧ P.BID = B.BID)}
a. All fields from Boat where size > 7 or Color = Blue
b. Size and Name from Boat where size > 7 and Color = Blue
c. Size and Name from Boat where size > 7 or Color = Blue
d. Only the Primary Key BID from Boat where size > 7 and Color = Blue
d. Only the Primary Key BID from Boat where size > 7 and Color = Blue
Explanation: Here the conditions are color must be blue AND size should be more
than 7. Also, only the BID column is ‘copied’ to the set P that is returned. So d is
true.
Note: {P / ∃B ∈ Boat ( B.SIZE >7 ∧ B.COLOR = ’Blue’ ∧ P.BID = B.BID)}
= Find all P where there exists BOATS in BOAT where boatsize is > 7 and boatcolor is Blue and obtain only the BID column.
Given these tables:
Sailor (SID, NAME, RATING, AGE)
Boat (BID, NAME, SIZE, COLOR)
WorksOn (SID, BID)
Which of the following statements is true for a superkey for the table Sailor?
a. SID is the only superkey
b. If and only if all sailors names were unique, SID, NAME together could form a
superkey
c. SID,NAME,RATING,AGE is a valid superkey
d. There is no superkey in this table.
c. SID,NAME,RATING,AGE is a valid superkey
Explanation: A superkey uniquely identifies a record of a table, but does not have to
be minimal. In this case, for the table Sailor any key that includes SID (the primary
key) is a superkey. So, c is the answer.
Note: Other superkeys would be (SID,NAME), (SID, RATING), (SID, AGE), (SID, NAME, RATING) and so on.
Given a table ABCD, which of the following is a lossless join decomposition?
AB is the primary key
a. R1= ACD, R2, ABC
b. R1= ABC, R2= ABD
c. R1= AB, R2= CD
d. R1= ABC, R2= AD
a. R1= ACD, R2, ABC - CORRECT
b. R1= ABC, R2= ABD - CORRECT
c. R1= AB, R2= CD - WRONG
d. R1= ABC, R2= AD - CORRECT
Explanation: A decomposition {R1 , R2} of R is lossless if and only if the common attributes of R1 and R2 form a superkey for either schema, that is
•R1 ∩ R2→R1
OR
•R1 ∩ R2→R2
As we can see, R1 and R2 in c., do not share common attribute.
Which of the following is an example of a trivial functional dependency?
a. X → A where A∈X
b. X → A, X → B, then X → AB
c. If X → Y, then XZ → YZ
d. X → Y , Y → Z then Z → X
a. X → A where A∈X
Explanation: The Trivial dependency is a set of attributes which are called a trivial if the set of attributes are included in that attribute.
So, X -> Y is a trivial functional dependency if Y is a subset of X.
Note: (X determines A) where (A is an element/subset of X)
Which of the following is NOT a problem that is associated with decomposition
and schema normalization?
a. Queries may become more expensive, as they may need joining decomposed
relations
b. Decomposition may cause loss of data tuples from the relations
c. Decomposition may introduce extra tuples from the relation.
d. Decomposition may cause us to lose functional dependencies from the
original relation.
b. Decomposition may cause loss of data tuples from the relations
Explanation: Decomposition breaks the table into parts. If a query then needs to use data from the decomposed tables, adding a join may make it more expensive.
Thus, a is true. C and D correspond to lossless join decomposition and dependency preserving decomposition. b is false (and is the answer)
See lecture 10 slides 27-31
Convert the following functional dependencies into minimal cover: A→B, AB→C
a. It is already a minimal cover
b. A→B, A→C
c. A→C, B→C
d. None of these
b. A→B, A→C
Explanation: A→B, AB→C is given. As we can get B from A (A→B), so in AB→C, B
on the left side is not necessary. So can be removed in the minimal form, giving us (b) as the answer.
Which of the following statements about using indexes in MySQL is true?
a. Indexes can only be created on individual columns, not a combination of
columns.
b. Doubling the number of indexes in a MySQL database speeds up update
operations tenfold times.
c. It is not possible to create more than one index on the same table in MySQL
database.
d. None of the above
d. None of the above
Option D: Correct because it is possible to make a combination of indexes and more than one index can be created on the same table.
Doubling the number of indexes in a MySQL database doesn’t speed up update operations tenfold times.
Consider a B+ tree in which the maximum number of keys in a node is 7. What is the minimum number of keys in any non-root node?
a. 1
b. 2
c. 3
d. 4
c. 3
Explanation:
maximum number of keys = 7
order = 7 + 1 = 8
keys = (order / 2) - 1 = (8/2) - 1 = 3
Note: Order m = 7 Max Child = m = 7 Min Child = ceil(m/2) = 4 Max keys = m - 1 = 6 Min keys = ceil(m/2) - 1 = ceil(3.5) - 1 = 4 - 1 = 3.
The unit of information read from and written to disk is a _________
a. Leaf
b. Page
c. Attribute
d. Value
b. Page
Option B: Correct. A page is a basic unit of I/O operation that stores every piece of data in SQL Server.
Note: Leaf is something that belongs to a tree data structure.
Attribute is the same as a column name attatched to an entity
A value could be something attached to a key in a key-value pair.
Suppose we have two transactions T1 and T2, the operation nr indicates when the operations run.
X and Y start as X = 500, Y=500.
After the two transactions X = 550, Y = 450 and Z = 1050.
Which ACID principle is violated?
Operation nr T1\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_T2 1 READ(X) 2 X := X+50 3 WRITE(X) 4 READ(Y)\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_READ(X) 5 Y := Y-50\_\_\_\_\_\_\_\_\_\_\_\_\_\_READ(Y) 6 WRITE(Y)\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Z := X+Y 7\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_WRITE(Z)
a. A
b. C
c. I
d. D
c. I
Explanation: As described in the text the result of the transaction is X=550, Y=450
z=1050. In line 5 Y is written and Y is used in T2. This results in an Isolation
violation.
Note: A - The entire transaction took place
C - The database is consistent before and after the transaction
I - The Y transaction in T1 was NOT isolated which violated this principle.
D - There was no failure.
ACID, What does the C mean?
a.Consistency;The database must be consistent before and after a
transaction.
b. Cooperative; All systems in the DBMS work together in a transparent way to
the user.
c. Concise; All transactions are minimized to the minimum operations that still
perform the desired task.
d. Catalog; The DBMS maintains a catalog consisting of metadata.
a.Consistency;The database must be consistent before and after a
transaction.
ACID, What does the I mean?
a. Integrity; The database maintains integrity.
b.Isolation;Multiple Transactions occur independently without
interference.
c. Inversion; DBMS transactions are reversible.
d. Inference; The DBMS should always be able to infer lost data.
b.Isolation;Multiple Transactions occur independently without
interference.
Consider the following sequence of actions; What does the ROLLBACK do ?
TRANSACTION …
COMMIT;
ROLLBACK;
a. Undoes the committed changes.
b. Commits the changes and makes its changes permanent.
c. Nothing.
d. Recommits the changes
c. Nothing.
Explanation: In this case the commit happens before the rollback. This means that the rollback has nothing to do
We have the table below Customers. You need to create a couple of stored
procedures.
pid pname ptlf pemail page
1 Mark 90000001 mark@email.com 22
2 Maria 90000002 maria@email.com 23
Create a stored procedure that retrieves the customers that are older
than the input variable age. (2)
DELIMITER \$\$ CREATE PROCEDURE getCostumerWithAge(IN Age INT) BEGIN START TRANSACTION; GET * FROM Customers WHERE page > Age COMMIT; END\$\$ DELIMITER ;
We have the table below Customers. You need to create a couple of stored
procedures.
pid pname ptlf pemail page
1 Mark 90000001 mark@email.com 22
2 Maria 90000002 maria@email.com 23
DELIMITER \$\$ CREATE PROCEDURE getCostumerWithAge(IN Age INT) BEGIN START TRANSACTION; GET * FROM Customers WHERE page > Age COMMIT; END\$\$ DELIMITER ;
Write the query to run the stored procedure, and retrieve all the
customers that are older than 18. (2)
CALL getCostumerWithAge(18);
We have the table below Customers. You need to create a couple of stored
procedures.
pid pname ptlf pemail page
1 Mark 90000001 mark@email.com 22
2 Maria 90000002 maria@email.com 23
Create a table named CustomersTest which has the same schema as
the Customers table. (1)
CREATE TABLE CustomersTest Like Customers;
The student can also recreate the table from scratch.
We have the table below Customers. You need to create a couple of stored
procedures.
pid pname ptlf pemail page
1 Mark 90000001 mark@email.com 22
2 Maria 90000002 maria@email.com 23
The developers that use the table Customers, need a test table that is
larger than the current table, to test the performance. You need to
create a stored procedure that creates n number of dummy customers
that will be put into the table Customers.
DELIMITER $$
CREATE PROCEDURE InsertEmp(IN NumRows INT)
BEGIN
DECLARE i INT DEFAULT 1;
START TRANSACTION;
WHILE i <= NumRows DO
INSERT INTO Customers(pname, ptlf, pemail, page)
VALUES
(CAST(RAND()99999, NCHAR) , FLOOR(RAND()99999999) ,
CAST(RAND()99999, NCHAR)+”@email.com”, FLOOR(RAND()99) );
SET i = i + 1;
END WHILE;
COMMIT;
END$$
DELIMITER ;
Consider the following schema:
Suppliers( sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog( sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by Suppliers. Suppliers sid:int sname:varchar(255) address:varchar(255)
Parts
pid: int
pname: varchar(255)
color: varchar(255)
Catalog
sid: int
pid: int
cost: real
Write the following query in SQL:
a. Create the needed tables. Add the necessary constraints. (2)
Solution Proposal:
a. Create the needed tables. Add the necessary constraints.
First, we need to create 3 tables: Suppliers, Parts and Catalog. The Suppliers table
has 3 attributes (sid, snake, address). The Parts table has three attributes (pid,
pname, color) and the Catalog table has three attributes (sid, pid, cost).
CREATE TABLE IF NOT EXISTS Exam2021.Suppliers(SID INT NOT NULL auto_increment,Sname VARCHAR(25),Address VARCHAR(50),
PRIMARY KEY (SID)
);
CREATE TABLE IF NOT EXISTS Exam2021.Parts(PID INT NOT NULL auto_increment,Pname VARCHAR(25),Color VARCHAR(25),
PRIMARY KEY (PID)
);
CREATE TABLE IF NOT EXISTS Exam2021.Catalog(SID INT NOT NULL,PID INT NOT NULL,Cost REAL,
FOREIGN KEY (SID) REFERENCES Suppliers(SID),
FOREIGN KEY (PID) REFERENCES Parts(PID)
);
Consider the following schema:
Suppliers( sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog( sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by Suppliers. Suppliers sid:int sname:varchar(255) address:varchar(255)
Parts
pid: int
pname: varchar(255)
color: varchar(255)
Catalog
sid: int
pid: int
cost: real
Write the following query in SQL:
How would you write queries to insert into the various tables in this database?
insert into Suppliers values (1, ‘Mr Meeseeks’,’Seattle’);
insert into Suppliers values (2, ‘Rick Sanchez’,’Illinois’)
insert into Parts values (1, ‘Bolt’,’Red’);
insert into Parts values (2, ‘Screw’,’Blue’);
insert into Catalog values (1, 1, 123);
insert into Catalog values (2, 2, 1200);
Consider the following schema:
Suppliers( sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog( sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by Suppliers. Suppliers sid:int sname:varchar(255) address:varchar(255)
Parts
pid: int
pname: varchar(255)
color: varchar(255)
Catalog
sid: int
pid: int
cost: real
Write the following query in SQL:
How would you write queries to find the pnames of parts for which there is some supplier.
Find the pnames of parts for which there is some supplier.
From lecturer answer suggestion:
SELECT Pname FROM Parts
WHERE Parts.PID = (SELECT PID FROM Catalog WHERE Catalog.PID =
Parts.PID);
I believe this is wrong, this will give error on to many rows returned for the “=” operator. The “=” should be replaced by the IN operator.
OR
SELECT pname FROM parts p INNER JOIN catalog c ON p.pid = c.pid;
