Database Design Final Exam
Examine
the following entity and decide which attribute breaks the 2nd Normal Form
rule:
ENTITY: RECEIPT
ATTRIBUTES:
#CUSTOMER ID
#STORE ID
STORE LOCATION
DATE
STORE
LOCATION (*)
CUSTOMER
ID
STORE
ID
DATE
When is an entity in 2nd Normal
Form?
When
all non-UID attributes are dependent upon the entire UID. (*)
When
attributes with repeating or multi-values are present.
When no
attritibutes are mutually independent and all are fully dependent on the
primary key.
None of
the Above.
To resolve a 2nd Normal Form
violation, we:
Move
the attribute that violates 2nd Normal Form to a new ERD.
Move
the attribute that violates 2nd Normal Form to a new entity with a relationship
to the original entity. (*)
Delete
the attribute that was causing the violation.
Do
nothing, an entity does not need to be in 2nd Normal Form.
When all attributes are
single-valued, the database model is said to conform to:
4th
Normal Form
1st
Normal Form (*)
3rd
Normal Form
2nd
Normal Form
An entity ORDER has the
attributes Order ID, Order Date, Product id, Customer ID. This entity is in 1st
Normal Form. True or False?
True
False
(*)
To convert an entity with a multi valued attribute to 1st
Normal Form, we create an additional entity and relate it to the original
entity with a 1:1 relationship. True or False?
True
False
(*)
When data is stored in more than
one place in a database, the database violates the rules of ___________.
Normalcy
Replication
Decency
Normalization
(*)
Examine the following Entity
and decide which sets of attributes break the 3rd Normal Form rule:
ENTITY: TRAIN
ATTRIBUTES:
TRAIN ID
MAKE
DRIVER ID
DRIVER NAME
DATE OF
MANUFACTURE
TRAIN
ID, MAKE
DRIVER
ID, DRIVER NAME (*)
MAKE,
DATE OF MANUFACTURE
None of
the above, the entity is already in 3rd Normal Form.
Examine the following Entity
and decide which rule of Normal Form is being violated:
ENTITY: CLIENT
ATTRIBUTES:
# CLIENT ID
FIRST NAME
LAST NAME
ORDER ID
STREET
ZIP CODE
1st
Normal Form. (*)
2nd
Normal Form.
3rd
Normal Form.
None of
the above, the entity is fully normalised.
As a database designer, you do
not need to worry about where in the datamodel you store a particular
attribute; as long as you get it onto the ERD, your job is done. True or False?
True
False
(*)
When any
attribute in an entity is dependent on any other non-UID attribute in that
entity, this is known as:
Non-dependency
Transitive
dependency (*)
Functional
dependency
Dependency
A unique identifier can only be
made up of one attribute. True or False?
True
False
(*)
A UID can be made up from the
following: (Choose Two)
Relationships
(*)
Synonyms
Entities
Attributes
(*)
A candidate UID that is not
chosen to be the Primary UID is called:
Artificial
Secondary
(*)
Simple
Composite
If an entity has no attribute
suitable to be a Primary UID, we can create an artificial one. True or False?
True
(*)
False
Arcs are Mandatory in Data modeling. All ERD's must have at
least one Arc. True or False?
True
False
(*)
Which of the following can be
added to a relationship?
An
attribute
An
optional attribute can be created
A
composite attribute
An arc
can be assigned (*)
Arcs are used to visually
represent _________ between two or more relationships in an ERD.
Inheritance
Exclusivity
(*)
Sameness
Differences
All relationships participating
in an arc must be mandatory. True or False?
True
False
(*)
Cascading UIDs are a feature
often found in what type of Relationship?
General
Relationship
Invalid
Relationship
Recursive
Relationship
Heirarchical
Relationship (*)
A
Hierarchical relationship is a series of relationships that reflect entities
organized into successive levels. True or False?
True
(*)
False
A relationship between an entity
and itself is called a/an:
Recursive
Relationship (*)
Heirarchical
Relationship
Invalid
Relationship
General
Relationship
A Recursive Relationship is
represented on an ERD by a/an:
Single
Toe
Crow's
Foot
Dog's
Tail
Pig's
Ear (*)
A particular problem may be
solved using either a Recursive Relationship or a Hierarchical Relationship,
though not at the same time. True or False?
True
(*)
False
Which of the following would be a
logical constraint when modeling time for a City entity?
If you
are doing a system for any French City, you would need security clearance.
People
are born in the city and people die in the city.
Cites
may change their names and/or country association if the borders of a country
change. (*)
Daily
traffic patterns must be monitored to determine how many law enforcement
officers are needed.
Modeling
historical data is optional. True or False?
True
(*)
False
Which of the following scenarios
should be modeled so that historical data is kept? (Choose two)
CUSTOMER
and PAYMENTS (*)
CUSTOMER
and ORDERS (*)
TEACHER
and AGE
BABY
and AGE
Which of the following scenarios
should be modeled so that historical data is kept? (Choose two)
STUDENT
and GRADE (*)
LIBRARY
and NUMBER OF BOOKS
STUDENT
and AGE
LIBRARY
and BOOK (*)
Why would you want to model a
time component when designing a system that lets people buy bars of gold?
Sales
people must determine where the gold is coming from.
You
would not want to model this; it is not important.
The
price of gold fluctuates and, to determine the current price, you need to know
the time of purchase. (*)
The
Government of your country might want to be notified of this transaction.
You are doing a data model for a
computer sales company where the price of postage depends upon the day of the
week that goods are shipped. So shipping is more expensive if the customer
wants a delivery to take place on a Saturday or Sunday. What would be the best
way to model this?
Update
the prices in the system, print out the current prices when they change, and
pin them on the company noticeboard.
Email
current prices to all employees whenever a price changes.
Use a
Delivery Day entity, which holds prices against week days, and ensure the we
also have an attribute for the Requested Delivery Day in the Order Entity. (*)
Allow
them to enter whatever delivery charge they want.
In an
ERD, High Volume Entities usually have very few relationships to other
entities. True or False?
True
False
(*)
You must make sure all entities
of a proposed system can fit onto one diagram. It is not allowed to break up a
data model into more than one diagram. True or False?
True
False
(*)
The explanation below is an
example of which constraint type?
A primary key must be unique, and no part of the primary key
can be null.
Entity
integrity (*)
User-defined
integrity
Referential
integrity
Column
integrity
A foreign key always refers to a
primary key in the same table. True or False?
True
False
(*)
Identify all of the incorrect
statements that complete this sentence: A primary key is...(Choose three)
One or
more columns in a table that uniquely identifies each row in that table.
A
single column that uniquely identifies each column in a table. (*)
Only
one column that must be null. (*)
A set of
columns in one table that uniquely identifies each row in another table. (*)
The explanation below is an example of which constraint
type?
The value in the dept_no column of the EMPLOYEES table must
match a value in the dept_no column in the DEPARTMENTS table.
Column
integrity
Entity
integrity
Referential
integrity (*)
User-defined
integrity
A table must have a primary key.
True or False?
True
False
(*)
When mapping supertypes,
relationships at the supertype level transform as usual. Relationships at
subtype level are implemented as foreign keys, but the foreign key columns all
become mandatory. True or False?
True
False
(*)
Which of the following is a valid
reason for considering a Subtype Implementation?
The
common access paths for the supertypes are different.
The
resulting table will reside in a single database and be used by just ONE user.
Most of
the relationships are at the supertype level.
Business
functionality, business rules, access paths, and frequency of access are all
very different between the subtypes. (*)
In an Oracle database, why would
the following table name not be allowed 'EMPLOYEE JOBS'?
JOBS is
a reserved word
The
database does not understand all capital letters
You
cannot have spaces between words in a table name (*)
EMPLOYEE
is a reserved word
In a physical data model, a relationship is represented as
a:
Primary
Key
Foreign
Key (*)
Column
Unique
Identifier
Why would this table name NOT
work in an Oracle database? this_year_end+next_year
Table
names must begin with an alphabetic character.
It is
too long.
The
Plus sign + is not allowed in object names. (*)
None of
the above.
The transformation from an ER
diagram to a physical design involves changing terminology. Entities in the ER
diagram become __________ :
Columns
Foreign
Keys
Unique
Keys
Tables
(*)
One-to-One relationships are
transformed into Foreign Keys in the tables created at either end of that
relationship. True or False?
True
False
(*)
In a physical model, many to many
relationships are resolved via a structure called a(n): ________________
Intersection
Entity
Subtype
Intersection
Table (*)
Supertype
Relationships on an ERD can only be transformed into UIDs in
the physical model? True or False?
True
False
(*)
An Arc is transformed to the
physical model by adding a foeign Key for every relationship in the Arc. True
or False?
True
(*)
False
A barrred Relationship will result
in a Foreign Key column that also is part of:
The
Check Constraint
The
Primary Key (*)
The
Column Name
The
Table Name
In which phases of the System
Development Life Cycle will we need to use SQL as a language? (Choose Two)
Transition
(*)
Build
and Document (*)
Strategy
Analysis
The data model can be used to...
Communicate
and confirm
Describe
and specify
Analyze
and refine
All of
the Above (*)
No comments:
Post a Comment