4.

Database Systems Design and Implementation

===

--- The Entity-Relationship Model ---

1. An ________ is a set of entities of the same type that share the same
properties, or attributes.
a) Entity set
b) Attribute set
c) Relation set
d) Entity model
Answer: a
Explanation: An entity is a “thing” or “object” in the real world that is
distinguishable from all other objects.

2. Entity is a _________
a) Object of relation
b) Present working model
c) Thing in real world
d) Model of relation
Answer: c
Explanation: For example, each person in a university is an entity.

3. The descriptive property possessed by each entity set is _________

a) Entity
b) Attribute
c) Relation
d) Model
Answer: b
Explanation: Possible attributes of the instructor entity set are ID, name, dept
name, and salary.

4. The function that an entity plays in a relationship is called that entity’s

_____________
a) Participation
b) Position
c) Role
d) Instance
Answer: c
Explanation: A relationship is an association among several entities.

5. The attribute name could be structured as an attribute consisting of first name,

middle initial, and last name. This type of attribute is called
a) Simple attribute
b) Composite attribute
c) Multivalued attribute
d) Derived attribute
Answer: b
Explanation: Composite attributes can be divided into subparts (that is, other
attributes).

6. The attribute AGE is calculated from DATE_OF_BIRTH. The attribute AGE is

a) Single valued
b) Multi valued
c) Composite
d) Derived
Answer: d
Explanation: The value for this type of attribute can be derived from the values of
other related attributes or entities.
7. Not applicable condition can be represented in relation entry as
a) NA
b) 0
c) NULL
d) Blank Space
Answer: c
Explanation: NULL always represents that the value is not present.

8. Which of the following can be a multivalued attribute?

a) Phone_number
b) Name
c) Date_of_birth
d) All of the mentioned
Answer: a
Explanation: Name and Date_of_birth cannot hold more than 1 value.

9. Which of the following is a single valued attribute

a) Register_number
b) Address
c) SUBJECT_TAKEN
d) Reference
Answer: a
Explanation: None.

10. In a relation between the entities the type and condition of the relation
should be specified. That is called as______attribute.
a) Desciptive
b) Derived
c) Recursive
d) Relative
Answer: a
Explanation: Consider the entity sets student and section, which participate in a
relationship set takes. We may wish to store a descriptive attribute grade with the
relationship to record the grade that a student got in the class.

--- Constraints ---

1. _____________ express the number of entities to which another entity can be
associated via a relationship set.
a) Mapping Cardinality
b) Relational Cardinality
c) Participation Constraints
d) None of the mentioned
Answer: a
Explanation: Mapping cardinality is also called as cardinality ratio.

2. An entity in A is associated with at most one entity in B, and an entity in B is

associated with at most one entity in A.This is called as
a) One-to-many
b) One-to-one
c) Many-to-many
d) Many-to-one
Answer: b
Explanation: Here one entity in one set is related to one one entity in other set.

3. An entity in A is associated with at most one entity in B. An entity in B,

however, can be associated with any number (zero or more) of entities in A.
a) One-to-many
b) One-to-one
c) Many-to-many
d) Many-to-one
Answer: d
Explanation: Here more than one entity in one set is related to one one entity in
other set.

4. Data integrity constraints are used to:

a) Control who is allowed access to the data
b) Ensure that duplicate records are not entered into the table
c) Improve the quality of data entered for a specific property
d) Prevent users from changing the values stored in the table
Answer: c
Explanation: The data entered will be in a particular cell (i.e., table column).

5. Establishing limits on allowable property values, and specifying a set of

acceptable, predefined options that can be assigned to a property are examples of:
a) Attributes
b) Data integrity constraints
c) Method constraints
d) Referential integrity constraints
Answer: b
Explanation: Only particular value satisfying the constraints are entered in the
column.

6. Which of the following can be addressed by enforcing a referential integrity

constraint?
a) All phone numbers must include the area code
b) Certain fields are required (such as the email address, or phone number) before
the record is accepted
c) Information on the customer must be known before anything can be sold to that
customer
d) Then entering an order quantity, the user must input a number and not some text
(i.e., 12 rather than ‘a dozen’)
Answer: c
Explanation: None.

7. ______ is a special type of integrity constraint that relates two relations &
maintains consistency across the relations.
a) Entity Integrity Constraints
b) Referential Integrity Constraints
c) Domain Integrity Constraints
d) Domain Constraints
Answer: b
Explanation: None.

8. Which one of the following uniquely identifies the elements in the relation?
a) Secondary Key
b) Primary key
c) Foreign key
d) Composite key
Answer: b
Explanation: Primary key checks for not null and uniqueness constraint.

9. Drop Table cannot be used to drop a table referenced by a _________ constraint.

a) Local Key
b) Primary Key
c) Composite Key
d) Foreign Key
Answer: d
Explanation: Foreign key is used when primary key of one relation is used in
another relation.

10. ____________ is preferred method for enforcing data integrity

a) Constraints
b) Stored Procedure
c) Triggers
d) Cursors
Answer: a
Explanation: Constraints are specified to restrict entries in the relation.

--- Entity-Relationship Diagrams ---

1. Which of the following gives a logical structure of the database graphically?
a) Entity-relationship diagram
b) Entity diagram
c) Database diagram
d) Architectural representation
Answer: a
Explanation: E-R diagrams are simple and clear—qualities that may well account in
large part for the widespread use of the E-R model.

2. The entity relationship set is represented in E-R diagram as

a) Double diamonds
b) Undivided rectangles
c) Dashed lines
d) Diamond
Answer: d
Explanation: Dashed lines link attributes of a relationship set to the relationship
set.

3. The Rectangles divided into two parts represents

a) Entity set
b) Relationship set
c) Attributes of a relationship set
d) Primary key
Answer: a
Explanation: The first part of the rectangle, contains the name of the entity set.
The second part contains the names of all the attributes of the entity set.

4. Consider a directed line(->) from the relationship set advisor to both entity
sets instructor and student. This indicates _________ cardinality
a) One to many
b) One to one
c) Many to many
d) Many to one
Answer: b
Explanation: This indicates that an instructor may advise at most one student, and
a student may have at most one advisor.

5. We indicate roles in E-R diagrams by labeling the lines that connect ___________
to __________
a) Diamond , diamond
b) Rectangle, diamond
c) Rectangle, rectangle
d) Diamond, rectangle
Answer: d
Explanation: Diamond represents a relationship set and rectangle represents a
entity set.
6. An entity set that does not have sufficient attributes to form a primary key is
termed a __________
a) Strong entity set
b) Variant set
c) Weak entity set
d) Variable set
Answer: c
Explanation: An entity set that has a primary key is termed a strong entity set.

7. For a weak entity set to be meaningful, it must be associated with another

entity set, called the
a) Identifying set
b) Owner set
c) Neighbour set
d) Strong entity set
Answer: a
Explanation: Every weak entity must be associated with an identifying entity; that
is, the weak entity set is said to be existence dependent on the identifying entity
set. The identifying entity set is said to own the weak entity set that it
identifies. It is also called as owner entity set.

8. Weak entity set is represented as

a) Underline
b) Double line
c) Double diamond
d) Double rectangle
Answer: d
Explanation: Weak entity set is represented as a double rectangle in entity
relationship diagram.

9. If you were collecting and storing information about your music collection, an
album would be considered a(n) _____
a) Relation
b) Entity
c) Instance
d) Attribute
Answer: b
Explanation: An entity set is a logical container for instances of an entity type
and instances of any type derived from that entity type.

10. What term is used to refer to a specific record in your music database; for
instance; information stored about a specific album?
a) Relation
b) Instance
c) Table
d) Column
Answer: b
Explanation: The environment of database is said to be an instance. A database
instance or an ‘instance’ is made up of the background processes needed by the
database.

--- Reduction to Relational Schemas ---

Consider the following relational schemas and answer the questions below
The section relation
Course_id Sec_id Semester Year Building
BIO-101 1 Spring 2010 Painter
CS-102 4 Summer 2009 Packyard
EE-201 3 Fall 2010 Watson
FIN-301 1 Spring 2011 Richard
The teaches relation
Id Course_id Sec_id Semester Year
1001 CS-101 1 Fall 2009
1002 EE-201 2 Spring 2010
1003 FIN-301 3 Fall 2009
1004 BIO-101 1 Summer 2011
1. Which one of the following can be treated as a primary key in teaches relation?
a) Id
b) Semester
c) Sec_id
d) Year
Answer: a
Explanation: Here Id is the only attribute that has to have a unique entry.

2. The primary key in the section relation is

a) Course_id
b) Sec_id
c) Both Course_id and Sec_id
d) All the attributes
Answer: a
Explanation: Here Course_id is the only attribute that has a unique entries. So,
Course_id is the primary key in the section relation table.

3.
Sanfoundry Certification Contest of the Month is Live. 100+ Subjects. Participate
Now!
SELECT * FROM teaches WHERE Sec_id = 'CS-101';
Which of the following Id is selected for the following query?
a) 1003
b) 1001
c) None
d) Error message appears
Answer: d
Explanation: The value CS-101 matches the Course_id but not Id.

4.
SELECT Id, Course_id, Building FROM SECTION s AND teaches t WHERE t.year=2009;
Which of the following Id are displayed?
a) 1003
b) 1001
c) Both 1003 and 1001
d) Error message appears
Answer: c
Explanation: Two rows are select in the above query.
5. The query which selects the Course_id ‘CS-101’ from the section relation is
a) Select Course_id from section where Building = ‘Richard’;
b) Select Course_id from section where Year = ‘2009’;
c) Select Course_id from teaches where Building = ‘Packyard’;
d) Select Course_id from section where Sec_id = ‘3’;
Answer: b
Explanation: The year ‘2009’ should be selected from the section relation.

6.
CREATE TABLE SECTION
(Course_id VARCHAR (8),
Sec_id VARCHAR (8),
Semester VARCHAR (6),
YEAR NUMERIC (4,0),
Building NUMERIC (15),
PRIMARY KEY (course id, sec id, semester, YEAR),
FOREIGN KEY (course id) REFERENCES course);
Which of the following has an error in the above create table for the relation
section
a) Primary key (course id, sec id, semester, year)
b) Foreign key (course id) references course
c) Year numeric (4,0)
d) Building numeric (15)
Answer: d
Explanation: It should be replaced by Year Building varchar (15).

7. The relation with primary key can be created using

a) Create table instructor (Id, Name)
b) Create table instructor (Id, Name, primary key(name))
c) Create table instructor (Id, Name, primary key (Id))
d) Create table instructor ( Id unique, Name )
Answer: c
Explanation: The value Name cannot be a primary key.

8. How can the values in the relation teaches be deleted?

a) Drop table teaches;
b) Delete from teaches;
c) Purge table teaches;
d) Delete from teaches where Id =’Null’;
Answer: b
Explanation: Delete table cleans the entry from the table.

9. In the above teaches relation ” Select * from teaches where Year = ‘2010’”
displays how many rows?
a) 2
b) 4
c) 5
d) 1
Answer: a
Explanation: There are two tuples with the year is 2009.

10. The relation changes can be got back using ________ command.
a) Flashback
b) Purge
c) Delete
d) Getback
Answer: a
Explanation: Purge deletes the table and delete cleans the table entry.

--- Entity-Relationship Design Issues ---

1. Let us consider phone_number ,which can take single or several values . Treating
phone_numberas an _________ permits instructors to have several phone numbers
(including zero) associated with them.
a) Entity
b) Attribute
c) Relation
d) Value
Answer: a
Explanation: Treating a phone as an attribute phone_number implies that instructors
have precisely one phone number each.
2. The total participation by entities is represented in E-R diagram as
a) Dashed line
b) Double line
c) Double rectangle
d) Circle
Answer: b
Explanation: It is used to represent the relation between several attributes.

3. Given the basic ER and relational models, which of the following is INCORRECT?
a) An attribute of an entity can have more than one value
b) An attribute of an entity can be composite
c) In a row of a relational table, an attribute can have more than one value
d) In a row of a relational table, an attribute can have exactly one value or a
NULL value
Answer: c
Explanation: It is possible to have several values for a single attribute provide
it is a multi-valued attribute.

4. Which of the following indicates the maximum number of entities that can be
involved in a relationship?
a) Minimum cardinality
b) Maximum cardinality
c) ERD
d) Greater Entity Count
Answer: b
Explanation: In SQL (Structured Query Language), the term cardinality refers to the
uniqueness of data values contained in a particular column (attribute) of a
database table.

5. In E-R diagram generalization is represented by

a) Ellipse
b) Dashed ellipse
c) Rectangle
d) Triangle
Answer: d
Explanation: Ellipse represents attributes, rectangle represents entity.

6. What is a relationship called when it is maintained between two entities?

a) Unary
b) Binary
c) Ternary
d) Quaternary
Answer: b
Explanation: Binary word usually represents two attributes.

7. Which of the following is a low level operator?

a) Insert
b) Update
c) Delete
d) Directory
Answer: d
Explanation: Directory is a low level to word on in file system.

8. Key to represent relationship between tables is called

a) Primary key
b) Secondary Key
c) Foreign Key
d) None of the mentioned
Answer: c
Explanation: Primary key of one relation used as an attribute in another relation
is called foreign key.

9. A window into a portion of a database is

a) Schema
b) View
c) Query
d) Data dictionary
Answer: b
Explanation: View is a logical portion of a database which is needed by some users.

10. A primary key is combined with a foreign key creates

a) Parent-Child relation ship between the tables that connect them
b) Many to many relationship between the tables that connect them
c) Network model between the tables that connect them
d) None of the mentioned
Answer: a
Explanation: Using the two relationships mother and father provides us a record of
a child’s mother, even if we are not aware of the father’s identity; a null value
would be required if the ternary relationship parent is used. Using binary
relationship sets is preferable in this case.

--- Extended E-R Features ---

1. The entity set person is classified as student and employee. This process is
called _________
a) Generalization
b) Specialization
c) Inheritance
d) Constraint generalization
Answer: b
Explanation: The process of designating subgroupings within an entity set is called
specialization.

2. Which relationship is used to represent a specialization entity?

a) ISA
b) AIS
c) ONIS
d) WHOIS
Answer: a
Explanation: In terms of an E-R diagram, specialization is depicted by a hollow
arrow-head pointing from the specialized entity to the other entity.

3. The refinement from an initial entity set into successive levels of entity
subgroupings represents a ________ design process in which distinctions are made
explicit.
a) Hierarchy
b) Bottom-up
c) Top-down
d) Radical
Answer: c
Explanation: The design process may also proceed in a bottom-up manner, in which
multiple entity sets are synthesized into a higher-level entity set on the basis of
common features.

4. There are similarities between the instructor entity set and the secretary
entity set in the sense that they have several attributes that are conceptually the
same across the two entity sets: namely, the identifier, name, and salary
attributes. This process is called
a) Commonality
b) Specialization
c) Generalization
d) Similarity
Answer: c
Explanation: Generalization is used to emphasize the similarities among lower-level
entity sets and to hide the differences.

5. If an entity set is a lower-level entity set in more than one ISA relationship,
then the entity set has
a) Hierarchy
b) Multilevel inheritance
c) Single inheritance
d) Multiple inheritance
Answer: d
Explanation: The attributes of the higher-level entity sets are said to be
inherited by the lower-level entity sets.

6. A _____________ constraint requires that an entity belong to no more than one

lower-level entity set.
a) Disjointness
b) Uniqueness
c) Special
d) Relational
Answer: a
Explanation: For example, student entity can satisfy only one condition for the
student type attribute; an entity can be either a graduate student or an
undergraduate student, but cannot be both.

7. Consider the employee work-team example, and assume that certain employees
participate in more than one work team. A given employee may therefore appear in
more than one of the team entity sets that are lower level entity sets of employee.
Thus, the generalization is _____________
a) Overlapping
b) Disjointness
c) Uniqueness
d) Relational
Answer: a
Explanation: In overlapping generalizations, the same entity may belong to more
than one lower-level entity set within a single generalization.

8. The completeness constraint may be one of the following: Total generalization or

specialization, Partial generalization or specialization. Which is the default?
a) Total
b) Partial
c) Should be specified
d) Cannot be determined
Answer: b
Explanation: Partial generalization or specialization – Some higher-level entities
may not belong to any lower-level entity set.

9. Functional dependencies are a generalization of

a) Key dependencies
b) Relation dependencies
c) Database dependencies
d) None of the mentioned
Answer: a
Explanation: The subclasses are combined to form the superclass.
10. Which of the following is another name for a weak entity?
a) Child
b) Owner
c) Dominant
d) All of the mentioned
Answer: a
Explanation: A parent may be called as a strong entity.

--- Querying database part-1 DDL ---

SQL data definition for part of the university database.

CREATE TABLE department

(dept_name VARCHAR (20),
building VARCHAR (15),
budget NUMBER,
PRIMARY KEY (dept_name));

CREATE TABLE course

(course_id VARCHAR (7),
title VARCHAR (50),
dept_name VARCHAR (20),
credits NUMERIC (2,0),
PRIMARY KEY (course_id),
FOREIGN KEY (dept_name) __________ department);

CREATE TABLE instructor

(ID VARCHAR (5),
name VARCHAR (20) NOT NULL,
dept_name VARCHAR (20),
salary NUMERIC (8,2),
FOREIGN KEY (dept_name) _______ department);

CREATE TABLE SECTION

(course_id VARCHAR (8),
sec_id VARCHAR (8),
semester VARCHAR (6),
YEAR NUMERIC (4,0),
building VARCHAR (15),
room_number VARCHAR (7),
time_slot id VARCHAR (4),
PRIMARY KEY (course_id, sec_id, semester, YEAR),
FOREIGN KEY (_______) ______ course);

CREATE TABLE teaches

(ID VARCHAR (5),
course_id VARCHAR (8),
sec_id VARCHAR (8),
semester VARCHAR (6),
YEAR NUMERIC (4,0),
PRIMARY KEY (ID, course_id, sec_id, semester, YEAR),
FOREIGN KEY (course_id, sec_id, semester, YEAR) REFERENCES SECTION,
FOREIGN KEY (ID) _______ instructor);
Answer questions based on the above commands

1. Which is the main relation which is used in the university database which is
referenced by all other relation of the university?
a) Teaches
b) Course
c) Department
d) Section
Answer: c
Explanation: Department is the only relation which forms the main part of the
university database.

2. The department relation has the an entry budget whose type has to be replaced by
a) Varchar (20)
b) Varchar2 (20)
c) Numeric (12,2)
d) Numeric
Answer: c
Explanation: Department is the only relation which forms the main part of the
university database.

3. In the course relation, the title field should throw an error in case of any
missing title. The command to be added in title is
a) Unique
b) Not null
c) 0
d) Null
Answer: b
Explanation: By specifying not null the value cannot be left blank.

4. In the above DDL command the foreign key entries are got by using the keyword
a) References
b) Key reference
c) Relating
d) None of the mentioned
Answer: a
Explanation: References (table_name) give the prior table name for the entry.

5. Identify the error in the section relation

a) No error
b) Year numeric (4,0)
c) Building varchar (15)
d) Sec_id varchar (8)
Answer: a
Explanation: The building and the sec_id have varchar values and year is of numeric
type. So no such errors are found in the relation.

6. The following entry is given in to the instructor relation .

(100202,Drake,Biology,30000)
Identify the output of the query given
a) Row(s) inserted
b) Error in ID of insert
c) Error in Name of insert
d) Error in Salary of the insert
Answer: b
Explanation: The varchar(5) value cannot hold the entry 100202.

7. Which of the following can be used as a primary key entry of the instructor
relation.
a) DEPT_NAME
b) NAME
c) ID
d) All of the mentioned
Answer: c
Explanation: The value ID can only be primary key unlike dept_name which is used as
a foreign key.

8. In the section relation which of the following is used as a foreign key?

a) Course_id
b) Course_id,sec_id
c) Room_number
d) Course_id,sec_id,room_number
Answer: a
Explanation: Course_id is the only field which is present in the course relation.

9. In order to include an attribute Name to the teaches relation which of the

following command is used?
a) Alter table teaches include Name;
b) Alter table teaches add Name;
c) Alter table teaches add Name varchar;
d) Alter table teaches add Name varchar(20);
Answer: d
Explanation: The form of the alter table command is
alter table r add AD;
where r is the name of an existing relation, A is the name of the attribute to be
added, and D is the type of the added attribute.

10. To replace the relation section with some other relation the initial step to be
carried out is
a) Delete section;
b) Drop section;
c) Delete from section;
d) Replace section new_table ;
Answer: b
Explanation: Droping the table drops all the references to that table.

--- Querying database part-2 DML ---

The instructor relation
ID Name Dept_name Salary
10101 Hayley Comp.Sci. 65000
12121 Jackson Finance 90000
15151 Nathan Music 87000
22222 April Biology 73000
34345 Crick Comp.Sci. 100000
The course relation
Course_id Title Dept_name Credits
CS-101 Robotics Comp.Sci. 5
BIO-244 Genetics Biology 4
PHY-333 Physical Principles Physics 3
MUS-562 Music Video Production Music 2
FIN-101 Investment Banking Finance 3
Answer the questions based on the above relations

1. Which of the following command is used to display the departments of the

instructor relation?
a) Select * from instructor where Dept_name = Finance;
b) Select * from instructor ;
c) Select dept_name from instructor;
d) Select dept_name for instructor where Name=Jackson;
Answer: c
Explanation: Only one field is necessary for the query and where clause is not
needed for the selection.
2. How can we select the elements which have common Dept_name in both the
relation ?
a) Select * from instructor i , course c where i.Dept_name=c.Dept_name;
b) Select Dept name from instructor ,Course ;
c) Select * from instructor i , course c ;
d) Select Dept_name from instructor where Dept_name = NULL;
Answer: a
Explanation: Here only the common elements are displayed .

3. Select distinct Dept_name from instructor ;

How many row(s) are displayed ?
a) 4
b) 3
c) 5
d) Error
Answer: a
Explanation: Distinct keyword eliminates the the common Dept_name .

4. Suppose the Authority want to include a new instructor for the title
Neuroscience what command should be inserted ?
a) Insert into instructor values(12111,Emma,NeuroScience,200000);
b) Insert into course values(12111,Introduction,NeuroScience,2);
c)
Insert into instructor values(12111,Emma,Biology,200000);
Insert into course values(BIO-112,Introduction to Neuro Science,NeuroScience,2);
d) Insert into course values(12111,Emma,NeuroScience,200000);
Answer: c
Explanation: The values have to be inserted into both the relations to be intact .

5. If a person all the people in Music department gets fired which of the following
has to be performed on the instructor relation ?
a) Delete Dept_name=Music in instructor;
b) Delete from instructor where Dept_name=Music;
c) Remove Dept_name= Music
d) All of the mentioned
Answer: b
Explanation: Delete from table_name where condition .

6.
SELECT DISTINCT T.name
FROM instructor AS T, instructor AS S
WHERE T.salary > S.salary AND S.dept name = ’Comp.Sci.’;
What will be displayed as the value of name for the above query?
a) Hayley
b) Jackson
c) Hayley and Crick
d) Crick
Answer: d
Explanation: Only the greatest salary in Comp.Sci dept is selected for the query.

7.
SELECT Name
FROM instructor
WHERE salary > SOME (SELECT salary FROM instructor WHERE dept_name = 'Comp.Sci.');
How many rows are selected ?
a) 3
b) 4
c) 2
d) 1
Answer: d
Explanation: This displays the names of instructors with salary greater than that
of some (at least one) instructor in the Biology department .

8. How will you select the Names whose first letter is E ?

a)
SELECT Name
FROM instructor
WHERE Name LIKE ’A%;
b)
SELECT Name
FROM course
WHERE Name LIKE ’A%;
c)
SELECT Dept_name
FROM instructor
WHERE Name LIKE ’A%;
d)
SELECT Name
FROM instructor
WHERE Dept_name LIKE ’A%;
View Answer
Answer: a
Explanation: % is used to indicate that some characters may appear .

9. Which function is used to find the count of distinct departments?

a) Dist
b) Distinct
c) Count
d) Count,Dist
Answer: a
Explanation: Count (distinct ID) is the correct usage.

10. Which function is used to identify the title with Least scope?
a) Min(Credits)
b) Max(Credits)
c) Min(title)
d) Min(Salary)
Answer: a
Explanation: Max is used to find the highest element and Min is used to find the
lowest element

--- Atomic Domains ---

1. A domain is ______ if elements of the domain are considered to be indivisible
units.
a) Atomic
b) Subatomic
c) Substructure
d) Subset
Answer: a
Explanation: A set of names is an example of a nonatomic value.

2. Identify the composite attributes

a) Salary
b) Credits
c) Section_id
d) None of the mentioned
Answer: d
Explanation: Composite attributes, such as an attribute address with component
attributes street, city, state, and zip have nonatomic domains.

3. Consider the relation given below and ind the maximum normal form applicable to
them
i. R(A, B) WITH productions { A --> B }
ii. R(A, B) WITH productions { B --> A }
iii. R(A, B) WITH productions {A —> B, B --> A }
iv. R(A, B, C) WITH productions {A -->B, B --> A, AB --> C }
a) i, ii and iii are in 3NF and iv is in BCNF
b) i and ii are in BCNF and iii and iv are in 3NF
c) All are in 3NF
d) All are in BCNF
Answer: d
Explanation: One of the more desirable normal forms that we can obtain is Boyce–
Codd normal form (BCNF). It eliminates all redundancy that can be discovered based
on functional dependencies.

4. Which one is based on multi-valued dependency:

a) First
b) Second
c) Third
d) Fourth
Answer: d
Explanation: One of the more desirable normal forms that we can obtain is Boyce–
Codd normal form (BCNF). It eliminates all redundancy that can be discovered based
on functional dependencies.

5. If a relation is in BCNF, then it is also in

a) 1 NF
b) 2 NF
c) 3 NF
d) All of the mentioned
Answer: d
Explanation: Third normal form (3NF) relaxes this constraint slightly by allowing
certain nontrivial functionaldependencieswhose left side is not a superkey.

6. If every non-key attribute is functionally dependent primary key, then the

relation will be in
a) First normal form
b) Second normal form
c) Third form
d) Fourth normal form
Answer: b
Explanation: Third normal form (3NF) relaxes this constraint slightly by allowing
certain nontrivial functionaldependencieswhose left side is not a superkey.

7. If an attribute of a composite key is dependent on an attribute of the other

composite key, a normalization called _____ is needed.
a) DKNF
b) BCNF
c) Fourth
d) Third
Answer: b
Explanation: BCNF eliminates all redundancy that can be discovered based on
functional dependencies.

8. The term for information that describes what type of data is available in a
database is:
a) Data dictionary
b) data repository
c) Index data
d) Metadata
Answer: d
Explanation: Meta data is generally data about a data.

9. A data type that creates unique numbers for key columns in Microsoft Access is:
a) Autonumber
b) Boolean
c) Sequential key
d) Sequential number
Answer: a
Explanation: This can be taken as a primary key.

10. A dependency exist between two columns when

a) Together they constitute a composite key for the table
b) Knowing the value in one column determines the value stored in another column
c) The table is in 3NF
d) Together they constitute a foreign key
Answer: a
Explanation: Given a set F of functional dependencies on a schema, we can prove
that certain other functional dependencies also hold on the schema.