DBMS Sample Test2

    Write the candidate keys (type answer in lowercase) Acct_id Cust_id State_code Bal 100 C1 AZ 10000 200 C2 CA 65675 423 C1 MN 45646 111 C3 AZ 456456 100 C6 MN 65675 800 C2 TX 12133 500 C3 IL 123123

Write the primary  key (type answer in lowercase) Acct_id Cust_id State_code Bal 100 C1 AZ 10000 200 C2 CA 65675 423 C1 MN 45646 111 C3 AZ 456456 100 C6 MN 65675 800 C2 TX 12133 500 C3 IL 123123

The number of Fully FD’s >= No of FD’s   in a relation     

• A.

  True

• B.

  False

When a relation is in 3NF implies the relation does not have any insert anomalies 

• A.

  True

• B.

  False

Foreign key should be unique          

• A.

  True

• B.

  False

A relation is in 2NF implies all the determinants should be primary key

• A.

  True

• B.

  False

{ C1, C2 } -----> { C3}  implies C1, C2  dependent on C3  

• A.

  True

• B.

  False

No of unique keys >= No of primary keys in a relation 

• A.

  True

• B.

  False

A NOT NULL constraint can have duplicate values 

• A.

  True

• B.

  False

     C1----> C5  then what is determinant and dependent here?

• A.

  C1 Determinant C2 Dependent

• B.

  C2 Determinant C1 Dependent

• C.

  Both are dependent

• D.

  Both are Determinant

• E.

  None of the above

Referential  integrity should be maintained for each table  in RDBMS      

• A.

  True

• B.

  False

A relation is in  nNF implies it should be in (n-1)NF   

• A.

  True

• B.

  False

FD is also called left irreducible dependency      

• A.

  True

• B.

  False

Normal forms applied at the logical level        

• A.

  True

• B.

  False

If a column is a candidate key then it should be a super key

• A.

  True

• B.

  False

If a relation is in 2NF means update anomaly is eliminated

• A.

  True

• B.

  False

In a table If a primary key is composite then implies foreign key in that table is also composite

• A.

  True

• B.

  False

A table can have more than one foreign keys

• A.

  True

• B.

  False

In a relation all functional dependents should become a fully functional dependencies implies

• A.

  All the determinants are candidate keys

• B.

  All determinants consists of only one column

• C.

  The relation is in 3NF

• D.

  There are more than one candidate key

Dependent  is Primary key  implies

• A.

  There are more than one candidate key

• B.

  The relation contains full FD's

• C.

  There are more than one alternative keys

• D.

  There is only one super key

    The above relation is in --------------      

• A.

  3NF

• B.

  BCNF

• C.

  2NF

• D.

  None of the above

 Is the relation is in 3NF?

• A.

  Yes

• B.

  No

• C.

  Can't say

What is the attribute/s which acts as both determinant and dependent?(Type your answer in lower case)

Does the below table design contains any insert anomoly  movie_id movie_name Director_id Dir_name M1 ABC D1       KKK M2 DEF D2 EEE  M3 AAA D1   LLL M4 BBB D3 YYY M5 CCC D5 QQQ M6 DDD D4 MMM M7 EEE D5  PAS

• A.

  Yes

• B.

  No

• C.

  Can'y say

Ship(BoatId, NbrOfPools, YearMade, Tonage, CompanyId, CompanyName) The only functional dependencies for this table are: BoatId --> NbrOfPools, YearMade, Tonage, CompanyId, CompanyName CompanyId --> CompanyName CompanyName --> CompanyId Which Normal form is the Ship table in?

• A.

  1NF

• B.

  2NF

• C.

  3NF

• D.

  BCNF

• E.

  Can't say

Employee(EmpId, EmpSSNO, EmpName, EmpAddress, EmpSalary, EmpAge) EmpId --> EmpSSNO, EmpName, EmpAddress, EmpSalary, EmpAge EmpSSNO --> EmpId, EmpName, EmpAddress, EmpSalary, EmpAge Which Normal form is the Employee table in?

• A.

  0NF

• B.

  1NF

• C.

  2NF

• D.

  3NF

• E.

  BCNF

Books(Title, Author, YrPub, Publisher) Title --> Author, YrPub, Publisher   Sample Data Title              Author    YrPub  Publisher Cooking For Fun    H Clark   2001   Acme Press Home Repair Cheap  T Rich    2000   Basic Books Fix up your car    A Ford    1998   Acme Press Training Goldfish  E Ward    2001   Cooper Ltd Cooking Survival   H Clark   2001   Acme Press Selling Your Home  T Rich    1999   Basic Books   Which Normal form is the Books table in?

• A.

  0NF

• B.

  1NF

• C.

  2NF

• D.

  3NF

• E.

  Can't say

Cars(Vin, Make, Style, Price, Year, Color) Vin --> Make, Style, Price, Year, Color Sample Data Vin          Make   Style      Price Year  Color 1FD56GH8901X Ford   SUV       55,000 1965  Red 1HC21KQ0733G Chevy  Sports    18,200 1998  Green 3AS88JV9203K Dodge  Truck     13,200 1997  Silver 4PL72XD5431M Ford   Truck      5,500 1996  Blue 8BE24RU7385Y Ford   Sedan      5,500 1996  Blue 7JU69TD2485R Dodge  SUV       18,200 1998  Black 0LF83SA6540Z Dodge  Sedan        500 1990  Rust   Which Normal form is the Cars table in?

• A.

  0NF

• B.

  1NF

• C.

  2NF

• D.

  3NF

• E.

  BCNF

You are given the table below for a relation R(A,B,C,D,E). You do not know the functional dependencies for this relation. A B C D E ’a’ 122 1 ’s1’ ’a’ ’e’ 236 4 ’e2’ ’b’ ’a’ 199 1 ’b5’ ’c’ ’b’ 213 2 ’z8’ ’d’ Suppose this relation is decomposed into the following two tables: R1(A,B,C,D) and R2(A,C,E). Is this decomposition lossless?

• A.

  Yes this decomposition lossless

• B.

  No this decomposition lossy

• C.

  Can't say

• D.

  None of the above

You are given the following functional dependencies for the ”EMPLOYEE” relation. Explain whether the relation ”EMPLOYEE” is BCNF and 3NF? Database: EMPLOYEE(ssn, first-name, last-name, address, date-joined, supervisor-ssn) DEPARTMENT(dept-no, name, manager-ssn) WORKS-IN(employee-ssn, dept-no) INVENTORY(dept-no, item-id, quantity) ITEMS(item-id, item-name, type) Foreign keys: 1. EMPLOYEE.supervisor-ssn and WORKS-IN.employee-ssn point to EMPLOYEE.ssn. 2. WORKS-IN.dept-no and INVENTORY.dept-no point to DEPARTMENT.dept-no. 3. INVENTORY.item-id points to ITEMS.item-id.  

• A.

  BCNF

• B.

  3NF

• C.

  Can't say