CSE Final Review

Select from the options given below the superkeys for the following relation R (A, B, C, X, Y, W) --------------------- (B, C) All of the options are superkeys None of the options are superkeys (A, X, Y) (B, C, W) (A, B, C) (A, B, C, X, Y, W)

(B, C, W) (A, B, C, X, Y, W)

What is the best possible way to map the following ER diagram onto a relational schema? E1(A1, A2) E2(A3, A4) R1(A1) E1(A1, A2) E2(A3, A4, A1) E1(A1, A2) E2(A3, A4) R1(A1, A3) E1(A1, A2, A3) E2(A3, A4)

1(A1, A2) E2(A3, A4) R1(A1, A3)

The client has provided some additional requirements: Appointments are either DAY or NIGHT appointments Some NIGHT appointments are WEEKDAY or WEEKEND appointments; the weekday has 10% additional fee and weekend 20%. A TWO-TECH (2 technicians) appointment is either a day or night appointment, it has 30% fee increase. All DAY appointments can be REMOTE, ON-SITE, or both. There are no additional fees. For the following EER diagram identify the subclasses (example: DAY, NIGHT, REMOTE). Use uppercase letters only:

1. DAY 2. NIGHT 3. REMOTE 4. ON-SITE 5. TWO-TECH 6. WEEKDAY 7. WEEKEND

The relation R1, with atomic attributes, holds the following FDs : What is the highest normal form that this relation meets? (without violations) ------------------------------------------------------ 1NF BCNF None of them 3NF 2NF

1NF

What is a relational database schema?. Select the best option. The database data A database description The structure of a relation A database snapshot

A database description

What can we say about the ER Model below? Entity E1 is in partial participation with relationship R1 Entity E2 is in partial participation with relationship R1 R1 is an example of a 1-to-many relationship R1 is an example of a binary relationship All of the options are correct None of the options are correct

All of the options are correct

Which of these statements about keys is true? A superkey is a subset of the candidate keys A superkey is a minimal key Any key is a superkey, but not vice versa All the other statements are correct

Any key is a superkey, but not vice versa

The following ER diagram about a relationship between a painter and his/her paintings is incomplete. It needs to describe that a painting must be painted by one and only one painter, and one painter may have multiple paintings. Please provide the information below filling the blanks: Cardinality on the Painting side (1/N): Cardinality on the Painter side (1/M): Participation on the Painting side (Partial/Total): Participation on the Painter side (Partial/Total):

Cardinality on the Painting side (1/N): N Cardinality on the Painter side (1/M): 1 Participation on the Painting side (Partial/Total): Total Participation on the Painter side (Partial/Total): Partial

What is desired when decomposing relations? Dependencies are preserved and there is no loss of information The data with anomalies is removed and dependencies may be preserved New dependencies are created to replace the old ones All dependencies are removed and data anomalies are preserved

Dependencies are preserved and there is no loss of information

What is the cause of the impedance mismatch when writing code within an application program to integrate it with a database? Differences between programming language data model and database data model None of the options Differences in the connection when accessing two or more databases Differences in time between developing the programming language model and developing the database model

Differences between programming language data model and database data model

Which of the following could be a possible relational model mapping for the below ER diagram? (Assume that keys with the same name in two different relations represent a foreign key mapping) E1(A1,A2), E2(A4, A2,A3) E1(A1,A2) E2(A3, A4) None of the other answers is valid E1(A1) E2(A1, A2, A3, A4) E1(A1) E2(A4,A3) R1(A2)

E1(A1) E2(A1, A2, A3, A4)

Which of the following will be be the relational model mapping for the EER diagram below? (Assume that keys with the same name in two different relations represent a foreign key mapping) Select all that apply. E1(A1, A3) E3(A2, A3, A4) E4(A2, A3, A5) E1(A1) E3(A1, A2, A3, A4) E4(A1, A2, A3, A5) E1(A1, A3) E2(A2, A3, A4, A5) None of the options are correct E1(A1) E2(A2, A3) E3(A3, A4) E4(A3, A5) All of the options presented are correct

E1(A1) E3(A1, A2, A3, A4) E4(A1, A2, A3, A5)

In the ER diagram below, what can we say about relationship R1? Entity C is in partial participation with R1 Entity C is in total participation with R1 R1 is an example of a third degree relationship R1 is an example of an identifying relationship

Entity C is in total participation with R1

In the EER diagram above, what can we say about relationship d Entity E4 is a disjointed subclass of E3 Entity E4 is a disjointed subclass of E2 Entity E3 is in partial participation with d d is an example of an identifying relationship None of the answers is true

Entity E4 is a disjointed subclass of E2

Which of the following are inner join operations?. Select all that apply. Left Outer Join Cross Product Equijoin Natural Join COUNT(*)

Equijoin Natural Join

The count aggregate function does not count duplicated values when counting the number of elements in a column (attribute). True False

False

The two problems caused by impedance mismatch, between the database model and programming language model: data sparsity and memory leaks, are handled by the Application Programming Interface. True False

False

Tree-based indexes are best for looking up values based on range tests because they keep the data on tables in sorted order. True False

False

Web database system administrators can avoid SQL-injection by installing and configuring security equipment, so hackers can't attack the database. True False

False

Websites administrators concerns about SQL-injection need to be handled by installing and configuring advanced security equipment to protect from hackers. True False

False

What views does a 3 schema database architecture consists of? Server, client, and network layers Physical, logical and export layers Internal, conceptual and external layers Application, Database and client layers

Internal, conceptual and external layers

The following EERD is intended for keeping track of college students that may hold different -sometimes combined- working positions. Select the option(s) that apply to complete the sub-classes relationship constraints: It should be disjoint (d) and total participation It should be overlapping (o) and total participation It should be overlapping (o) and partial participation It should be disjoint (d) and partial participation

It should be overlapping (o) and partial participation

Is the relation R shown below in 1NF? 2NF? It's not in 1NF It's in 1NF, but not in 2NF It's not in 1NF, but it's in 2NF It's in 2NF

It's not in 1NF

What is the following Relational Algebra query retrieving? List the names of all dependents of employees working in two or more Departments List the names of all dependents of employees with two or more dependents List the names of all employees with two or more dependents List the names of all employees with NO dependents

List the names of all employees with two or more dependents

What is the cardinality of the R relationship type shown in the diagram below? M:N Relationship (E side: M and D side: N) 1:N Relationship (E side: 1 and D side: N) 1:1 Relationship (E side: 1 and D side: 1) N:1 Relationship (E side: N and D side: 1)

N:1 Relationship (E side: N and D side: 1)

A sample relation and its current state is provided below. Use it to answer the questions below: According to the data in the sample relation provided above identify which of the following are superkeys: (Select all that apply) Credit Card #, Exp Date Credit Card #, Exp Date, Address Credit Card # Name Cust ID, Credit Card #, Exp Date Cust ID

Name Cust ID, Credit Card #, Exp Date Cust ID

Which of the following are structural constraints on relationships between entity sets? (Select Multiple Answers if necessary) Participation Cardinality Multi-valued attributes Candidate keys

Participation Cardinality

Identify the operation performed between the following two relations R1 and R3 that retrieves the values shown in the result of that operation. R1 - R3 PROJECT a, b, c (R3) R1 Intersect R3 R1 * R3

R1 Intersect R3

Identify the complete relational schema for the final ERD below. You need to identify all relations, their attributes, Primary Keys (PK) and Foreign Keys (FK) keys, entering them into the blank boxes according to the following example: Example: PROJECT(pno, dno) PK: (pno) FK: (dno) You must enter name of relation and then attributes first; then value(s) for PK and then values for FK (if any). R1 is ______ (____, ____, ____), PK: _____, FK: _____ R2 is _____ (____, ____), PK: _____ R3 is ______ (____, ____, ____), PK: (____, _____, ____), FK: ____ R4 is ____ (____), PK: ____ R5 is ____ (____, ____) PK: (____, ____), FK: (____, ____)

R1 is SERVICE (ticketid, type, email), PK: ticketid, FK: email R2 is PARTS (id, amt), PK: id R3 is APPT (ticketid, date, time), PK: (ticketid, date, time), FK: ticketid R4 is TECHNICIAN (email), PK: email R5 is USE (ticketid_, id) PK: (ticketid, id), FK: (ticketid, id)

Identify the operation performed between the following two tables R1 and R3 that retrieves the values shown in the resulting table. (See Answer Choices in Doc)

R1 ⨝ R1.B=R3.E R3

What is the following Relational Algebra query retrieving? Retrieves the names and ssn of all employees working on projects Retrieves the names and ssn of employees who do not work on projects Retrieves the names and ssn of all employess working on the company Retrieves the names of all employees and the SSN of project manager

Retrieves the names and ssn of employees who do not work on projects

Which of the following SQL queries corresponds to the following relational algebra operation: R * S ______________________________ SELECT * FROM R, S WHERE R=S SELECT * FROM R OUTER JOIN S ON R=S SELECT * FROM R, S SELECT * FROM R NATURAL JOIN S;

SELECT * FROM R NATURAL JOIN S;

Identify the SQL query performed with the two relations R1 and R3 that retrieves the values shown in the output. SELECT * FROM R1, R3 WHERE R1.A = R3.D; SELECT * FROM R1, R3 WHERE A=D AND B=E AND C=F; None of the options is correct SELECT * FROM R1, R3 WHERE R1.B = R3.E; SELECT * FROM R1, R3 WHERE R1.A IN (R3.D);

SELECT * FROM R1, R3 WHERE R1.B = R3.E;

Identify the SQL query performed with the two relations R1 and R3 that retrieves the values shown in the query result. None of them SELECT * FROM R1, R3 WHERE R1.A = R3.D; SELECT * FROM R1 OUTER JOIN R3 ON R1.A = R1.D; SELECT * FROM R1, R3; SELECT * FROM R1, R3 WHERE R1.A = R3.E;

SELECT * FROM R1, R3;

Which query shows the attributes of customers from the Customer table whose names start with S? SELECT * FROM Customer WHERE Customer_Name like '%S'; SELECT * FROM Custommer WHERE Customer_Name like '\%S'; SELECT * FROM Customer WHERE Customer_Name like '_S'; SELECT * FROM Customer WHERE Customer_Name like 'S%';

SELECT * FROM Customer WHERE Customer_Name like 'S%';

Identify the SQL query performed with the two relations R1 and R2, that retrieves the values shown in the result of that operation. SELECT A, B, C FROM R1, R2 SELECT A, B, C FROM R1 EXCEPT SELECT D, E, F FROM R2 SELECT A, B, C FROM R1, R2 WHERE A=D AND B=E AND C=F; SELECT A, B, C FROM R1 UNION SELECT D, E, F FROM R2

SELECT A, B, C FROM R1 UNION SELECT D, E, F FROM R2

What is the correct way to find the number of members with a Coach? SELECT COUNT(DISTINCT Coach) as CoachedMembers FROM Member; None of the options SELECT COUNT(*) as CoachedMembers FROM Member WHERE Coach != NULL; SELECT COUNT(*) as CoachedMembers FROM Member; SELECT COUNT(Coach) as CoachedMembers FROM Member;

SELECT COUNT(Coach) as CoachedMembers FROM Member;

Which of the following SQL query returns each Dependent, only once, from a table Dependent? SELECT DISTINCT Dependent FROM Dependent_name; None of them SELECT DISTINCT Dependent_name FROM Dependent; SELECT Dependent_Name FROM Dependent WHERE DISTINCT Dependent_name; SELECT Dependent_name FROM DISTINCT Dependent;

SELECT DISTINCT Dependent_name FROM Dependent;

We want to find those employees who participate in 2 or more projects. What is the correct way to do it? SELECT Essn, COUNT(Pno) as Count FROM Works_On WHERE COUNT(Pno) >= 2; SELECT Essn, COUNT(*) as Count FROM Works_On GROUB BY Essn HAVING COUNT(*) >= 2; SELECT Essn, COUNT(Pno) as Count > 2 FROM Works_On GROUP BY Essn; SELECT Essn, COUNT(Pno) as Count FROM Works_On GROUP BY Essn WHERE COUNT(Pno) >= 2;

SELECT Essn, COUNT(*) as Count FROM Works_On GROUB BY Essn HAVING COUNT(*) >= 2;

Match the term on the left with the definition: Specialization hierarchy: ______ Specialization lattice: __________ Answer choices: Every subclass participates in only one class/subclass relationship A subclass cannot be a subclass in one class/subclass relationship Subclass can be a subclass in more than one class/subclass relationship No subclasses participate in class/subclass relationship

Specialization hierarchy: Every subclass participates in only one class/subclass relationship Specialization lattice: Subclass can be a subclass in more than one class/subclass relationship

A stored procedure is a program module that can be called using the Call statement. They are ... (choose the option that apply) ------------------------------------------------------------ Stored in the database and Executed on the database server Stored in the client's operating system but not Executed on the database server Stored in the client and Executed on the database server Stored in database and Executed on the client computer

Stored in the database and Executed on the database server

A web developer needs to query the database with the identification provided by the member to validate the information. Which one of the following statements is most appropriate? All the options are appropriate String sql = "SELECT MemberId FROM Member WHERE MemberId=" + Id + " AND" + passwd =? + ";"; String sql = "SELECT MemberId FROM Member WHERE MemberId= " + Id + " ' AND passwd = ' " + password + " ' ;"; String sql = "SELECT MemberId FROM Member WHERE MemberId= " + Id? + " AND passwd = " + password? + ";"; None of the options is appropriate String sql = "SELECT MemberId FROM Member WHERE MemberId=? AND passwd =?;";

String sql = "SELECT MemberId FROM Member WHERE MemberId=? AND passwd =?;";

What relational algebra operation will be executed for the following SQL query: SELECT * FROM Table01, Table02; ------------------------- Table01 ⨝ Table02 Table01 * Table02 Table01 X Table02 Table01 U Table02

Table01 X Table02

In terms of completeness constraints in an Enhanced relationship model, a partial specialization means: That every entity in the superclass must be a member of some subclass in the specialization That an entity may not be part of one of the subclassess in that type of specialization That the subclasses are not constrained to be disjoint That the subclasses of the specialization must be disjointed

That an entity may not be part of one of the subclassess in that type of specialization

In the EER diagram below, what can we say about relationship R5? Entity E2 is in total participation with R5 The cardinality and participation in the E1 side is (1, M) The cardinality and participation in the E1 side is (0, M) Entity E2 is a weak entity None of the answers are true

The cardinality and participation in the E1 side is (0, M)

Suppose that a relation R1(A, B, C, D) has the set {B,D} as a superkey. Which of the following statements must always be true: D will be a candidate key of the relation The combination of B and D will have different values for each tuple in the relation B will be the primary key of the relation The set (B, D) is a minimal key of the relation

The combination of B and D will have different values for each tuple in the relation

A local computer maintenance business needs a database system to manage the technical services, appointments as well as the parts used and technicians that provide their services. According to the following ER diagram determine which of the partial list of requirements below are true. Select All that Apply: Technicians must provide multiple services There are simultaneous Appointments available, date and time must be saved for each one The constrain notation for the "provide" relationship in the Service side is (1, 1) Services will have a type and one appointment A Service may schedule multiple appointments Appointments have derived date and time values Appointments must be scheduled for one or many services

There are simultaneous Appointments available, date and time must be saved for each one The constrain notation for the "provide" relationship in the Service side is (1, 1) A Service may schedule multiple appointments

What would be the best index that helps execute the following query: SELECT COUNT(*) as TotSales FROM Sales as S WHERE S.city BETWEEN 'Columbus' AND 'Nashville' ---------------------------------------------------------- Tree index by City and TotSales Cluster by TotSales Tree index by City Hash index by City

Tree index by City

Is the relation R1 shown below in 1NF? True False

True

The count aggregate function counts duplicated values when counting the number of elements in a column (attribute). True False

True

This schedule is not serializable: r3(X); r2 (X); r1(X); w1(X); w3(X); True False

True

In the the following relational schema, A in R2 is a foreign key. Which of the following statements must be true? Attribute A in R2 should have a different name to avoid confusion Values for A in R1 must correspond to existing values for A in R2 Values for A in R2 may not be NULL Values for A in R2 must be the same type and in the same domain as A in R1

Values for A in R2 must be the same type and in the same domain as A in R1

When a relation is in 4NF? When a relation has a candidate key that is not superkey When a non-prime determines multiple values in another non-prime When for each attribute X that multi-determines values in another attributes, X is a superkey When the relation is 3NF and the primary key is a superkey

When for each attribute X that multi-determines values in another attributes, X is a superkey

If X is a candidate key for a relation R(X, Y, Z), which statement is true? There could be two or more tuples with the same values for X in R X is not a SuperKey for R (X, Y) is also a candidate key for R X is also a SuperKey

X is also a SuperKey

For the following relation R(X, Y, W, Z), Which functional dependencies may hold? W -> Z X -> Z Y -> Z X -> Y

Y -> Z

Match the symbol at the left with the corresponding name: Partial key attribute Compound Attribute Key attribute Derived attribute Derived relationship Weak attribute Disjoint relationship type Multivalued attribute

a Multivalued attribute b Disjoint relationship type c Derived attribute d Compound Attribute

Desirable properties of relational normalization include minimizing insertion/deletion anomalies minimizing redundancy minimizing update anomalies all of the options given

all of the options given

Match the term with the definition on the right from the drop-down. disjointess constraint: ______ overlapping constraint: _______ Answer choices: An entity can be a member of one or more of the sub-classes An entity is a member of only one of the super-classes An entity can be a member of at most one of the sub-classes An entity is not a member of the super-classes

disjointess constraint: An entity can be a member of at most one of the sub-classes overlapping constraint: An entity can be a member of one or more of the sub-classes

Match the JDBC API method at the right with the correct description statement at the left. getConnection(String url): ______ getMetaData(): _________ executeQuery(String sql): ______ conn.preparedStatement(String sql): __________ Answer Choices: A method to provide a SQL query to open a connection A method to provide a string and placeholders for a SQL query A method to execute a SQL update query A method to execute a SQL select query A method to establish database connection A method for getting data about performance met A method to get runtime information about JDBC features

getConnection(String url): A method to establish database connection getMetaData(): A method to get runtime information about JDBC features executeQuery(String sql): A method to execute a SQL select query conn.preparedStatement(String sql): A method to provide a string and placeholders for a SQL query

A candidate for a weak entity type .... has partial participation in an identifying relationship is the primary key attribute is a minimal SuperKey has total participation in an identifying relationship

has total participation in an identifying relationship

When we refer to a relation as an n-degree relation, the "n" refers to .. the number of tuples in the relation the number of attributes in the relation the number of relations in the database None of the options provided

the number of attributes in the relation

After analyzing the relation R1, the following FDs were identified: Determine the best/most complete classification for the following values on the left side with the options on the right drop-down list. Choose the key term option that best apply for each case according to the FDs. ----------------------------------------------------------------------- {X, B, Y, S, W, F, P}: _______ {X, B, Y, S}: ________ {X, B}: ________ {Y, S}: ________ { B }: __________ { W }: _________ Choices: Prime Attribute(s) Foreign Key Non-Prime Attribute(s) Candidate Key All Prime Attributes, and SuperKey Surrogate Key SuperKey Maximal Primary Key

{X, B, Y, S, W, F, P}: SuperKey {X, B, Y, S}: All Prime Attributes, and SuperKey {X, B}: Candidate Key {Y, S}: Candidate Key { B }: Prime Attribute(s) { W }: Non-Prime Attribute(s)

The relation R below has the functional dependencies shown. Which option(s) are correct? R (X, Y, A, B, C, D) {A} ⟶{X, Y, B, C, D} {Y} ⟶{X, A, B, C, D} ---------------------- {X} is a superkey {A, X} is a Key None of the options are valid {Y} is a candidate key {A, C} { X, Y, B, D }

{Y} is a candidate key {A, C} { X, Y, B, D }