Section 6 Design
ARTIFICIAL, COMPOSITE AND SECONDARY UID
People
are not born with "numbers," but a lot of systems assign student
numbers, customer IDs, etc. A shoe has a color, a size, a style, but may
not have a descriptive "number". So, to be able to uniquely and
efficiently identify one instance of the entity SHOE, a/an
______________ UID can be created.
Artificial
A unique identifier can only be made up of one attribute. True or False?
False
A UID can be made up from the following:
Attributes
Relationships
An entity can only have one UID. True or False?
False
NORMALIZATION AND FIRST NORMAL FORM
An entity can have repeated values and still be in 1st Normal Form. True or False?
False
When all attributes are single-valued, the database model is said to conform to:
1st Normal Form
The following entity is on 1st normal form: True or False?
ENTITY: VEHICLE
ATTRIBUTES:
REGISTRATION
MAKE
MODEL
COLOR
DRIVER
PASSENGER 1
PASSENGER 2
PASSENGER 3
False
When data is stored in more than one place in a database, the database violates the rules of ___________.
Normalization
SECOND NORMAL FORM
An entity can be on 2nd Normal Form even if it has repeated values. True or False?
False
What is the rule of Second Normal Form?
All non-UID attributes must be dependent upon the entire UID
Examine the following entity and decide which attribute breaks the 2nd Normal Form rule:
ENTITY: CLASS
ATTRIBUTES:
CLASS ID
DURATION
SUBJECT
TEACHER NAME AND ADDRESS
TEACHER NAME AND ADDRESS
THIRD NORMAL FORM
No databases in the world is ever truly on 3rd Normal Form. Everyone always stops after 2nd Normal Form. True or False?
False
Examine the following Entity and decide which sets of attributes breaks the 3rd Normal Form rule:
ENTITY: TRAIN (SYNONYM: ROLLING STOCK)
ATTRIBUTES:
TRAIN ID
MAKE
MODEL
DRIVER NAME
DEPARTURE STATION
NUMBER OF CARRIAGES
NUMBER OF SEATS
DATE OF MANUFACTURE
DEPARTURE STATION, DRIVER NAME
NUMBER OF CARRIAGES, NUMBER OF SEATS
As a database designer it is your job to store data in only one place and the best place. True or False?
True
Section 7 Design
ARCS
Which of the following is the definition for Third Normal Form?
No non-UID attribute can be dependent on another non-UID attribute
If
the entity CD has the attributes: #number, *title, *producer, *year, o
store name, o store address, this entity is in 3rd Normal Form ("no
non-UID attribute can be dependent on another non-UID attribute). True
or False?
False
Which of the following can be added to a relationship?
An arc can be assigned
To visually represent exclusivity between two or more relationships in an ERD you would most likely use an ________.
Arc
Which of the following would best be represented by an arc?
STUDENT ( University, Trade School)
Secondary UID's are
Useful as an alternative means identifying instances of an entity
All parts of a UID are mandatory. True or False?
True
This diagram could also be expressed as a supertype/subtype construction. True or False?
False
HIERARCHIES AND RECURSIVE RELATIONSHIPS
A recursive rationship should not be part of a UID. True or False?
True
Which of the following would be a good Unique Identifier for its Entity?
Identification Number for Person
Birthdate for Baby Which Includes Hour, Minute, and Seconds
A relationship can be both recursive and hierachal at the same time. True or False?
False
In this simple diagram, what comprises the unique identifier for the student class entity?
student id and course id
MODELING HISTORICAL DATA
Historical data should always be kept. True or False?
False
Which of the following scenarios should be modeled so that historical data is kept?
LIBRARY and BOOK
STUDENT and GRADE
Modeling historical data can produce a unique identifier that includes a date. True or False?
True
Audit
trail attributes cannot be placed in the entities they are auditing,
they must be placed in separate, new entities, created just for that
purpose. True or False?
False
Section 9 Design
MODELING CHANGE: TIME
How do you know when to use the different types of time in your design?
It depends on the functional needs of the system
It
is desirable to have an entity called DAY with a holiday attribute when
you want to track special holidays in a payroll system. True or False?
True
Which of the following would be a logical constraint when modeling time for a country entity?
Countries may need an end date in your system, because they can change fundamentally over time, e.g. Yugoslavia.
Modeling historical data produces efficient ways for a business to operate such as:
Providing valuable information via reports to management.
When you add the concept of time to your data model, you are:
Adding complexity to your model.
What is the benefit to the users of a system that includes "time," e.g. Start Date and End Date for Employees?
Increased usability and flexibility of a system; we can the trace e.g. the different managers an employee had over time.
If you are tracking employment dates for an employee, do you need to have an "End Date" attribute?
Yes, if the company wants to track employee information, like multiple start and end dates
MODELING CHANGE: PRICE
You
are doing a data model for a computer sales company, where the price
goes down on a regular basis. If you want to allow them to modify the
price and keep track of the changes, what is the best way to model this?
E. Both A and C
Why would you want to model a time component when designing a system that lets people buy bars of gold?
The price of gold fluctuates and for determining price, you need to know the time of purchase
What is the function of logging or journaling in conceptual data models?
Allows you to track the history of attribute values, relationships and/or entire entities
Which of the following is a logical constraint that could result from considering how time impacts an example of data storage?
An ASSIGNMENT may only refer to a COUNTRY that is valid at the Start Date of the ASSIGNMENT.
Section 10 Design
DRAWING CONVENTIONS FOR READABILITY
It
is a good idea to group your entities in a diagram according to the
expected volumes. By grouping high volume entities together, the
diagrams could become easier to read. True or False?
True
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?
False
There
are no formal rules for how to draw ERD's. The most important thing is
to make sure all entities, attributes and relationships are documented
on diagram. The Layout is not significant. True or False?
False
Which of the following statements are true for ERD's to enhance their readability.
There should be no crossing lines.
All crows feet (Many-ends) of relationships should point the same way.
GENERIC MODELING
Generic models are generally less complex than a specific model. True or False?
False
All data models MUST have some portions of the model modeled as a generic component. True or False?
False
When you transform a specific model to be generic, which of the following statements are true?
You tend to end up with fewer entities in the generic model than you had in the specific model
Either all or none of the original attributes make it into the generic model
Section 11 Design
INTRODUCTION TO RELATIONAL DATABASE CONCEPTS
Column integrity refers to
Columns always containing values consistent with the defined data format
One or more columns in a primary key can be null. True or False?
False
The explanation below is an example of what constraint type:
If
the value in the balance column of the ACCOUNTS table is below 100, we
must send a letter to the account owner which will require extra
programming to enforce.
User-defined integrity
Identify all of the correct statements that complete this sentence: A primary key is ...
A single column that uniquely identifies each row in a table
A set of columns that uniquely identifies each row in a table
A set of columns and keys in a single table that uniquely identifies each row in a single table
The explanation below defines which constraint type:
A primary key must be unique, and no part of the primary key can be null.
Entity integrity
A foreign key always refers to a primary key in the same table. True or False?
False
The explanation below defines which constraint type:
A column must contain only values consistent with the defined data format of the column
Column integrity
Foreign keys cannot be null when
It is part of a primary key
The explanation below is an example of what 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.
Referential integrity
A table does not have to have a primary key. True or False?
True
BASIC MAPPING: THE TRANSFORMATION PROCESS
In an Oracle database, why would the following table name not be allowed 'EMPLOYEE JOBS'?
You cannot have spaces between words in a table name
In a physical data model, an entity becomes a _____________.
Table
The
transformation from an ER diagram to a physical design involves
changing terminology. Entities in the ER diagram become __________ , and
attributes become ____________.
Tables, Columns
In a physical data model, a relationship is represented as a:
Foreign Key
Attributes become columns in a database table. True or False?
True
The
transformation from an ER diagram to a physical design involves
changing terminology. Relationships in the ER diagram become __________ ,
and primary unique identifiers become ____________.
Foreign keys, primary keys
Why would this table name NOT work in an Oracle database?
2007_EMPLOYEES
Table names must start with an alphabetic character
RELATIONSHIP MAPPING
Relationships on an ERD can only be transformed into UIDs in the physical model?
False
One-to-One relationships are transformed into Foreign Keys in the tables created at either end of that relationship?
False
One-to-Many Optional to Mandatory becomes a _______________ on the Master table.
Nothing (There are no new columns created on the Master table)
What do you create when you transform a many to many relationship from your ER diagram into a physical design?
Intersection table
A barrred Relationship will result in a Foreign Key column that also is part of:
The Primary Key
Two
entities A and B have an optional (A) to Mandatory (B) One-to-One
relationship. When they are transformed, the Foreign Key(s) is placed
on:
The table B
SUBTYPE MAPPING
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 optional. True or False?
True
When
translating an arc relationship to a physical design, you must turn the
arc relationships into foreign keys. What additional step must you take
with the created foreign keys to ensure the exclusivity principle of
arc relationships? (Assume that you are implementing an Exclusive
Design)
Make all relationships optional
Create an additional check constraint to verify that one foreign key is populated and the others are not
Which of the following are reasons you should consider when using a Subtype Implementation?
When the common access paths for the subtypes are different.
Most of the relationships are at the subtype level
The "Arc Implementation" is a synonym for what type of implementation?
Supertype and Subtype Implementation
Section 12 Design
SQL INTRODUCTION: QUERYING THE DATABASE
Examine the follolowing SELECT statement.
SELECT *
FROM employees;
This statement will retrieve all the rows in the employees table. True or False?
True
What command retrieves data from the database?
SELECT
What command can be used to show information about the structure of a table?
DESCRIBE
What command do you use to add rows to a table
INSERT
What command can be added to a select statement to return a subset of the data?
WHERE
BASIC TABLR MODIFICATIONS
The f_customers table contains the following data:
ID Name Address City State Zip
1 Cole Bee 123 Main Street Orlando FL 32838
2 Zoe Twee 1009 Oliver Avenue Boston MA 02116
3 Sandra Lee 22 Main Street Tampa FL 32444
If you run the following statement,
DELETE FROM F_CUSTOMERS
WHERE STATE='FL';
How many rows will be left in the table?
1
The SQL statement ALTER TABLE EMPLOYEES DROP COLUMN SALARY will delete all of the rows in the employees table. True or False?
False
What will the following statement do to the employee table?
ALTER TABLE employees ADD (gender VARCHAR2(1))
Add a new column called GENDER to the EMPLOYEES table
Section 13 Design
SYSTEM DEVELOPMENT LIFE CYCLE
During which phases of the System Development Life Cycle would you roll out the system to the users?
Transition and Production
In which phases of the System Development Life Cycle will we need to use SQL as a language?
Transition
Build and Document
The data model can be used to...
All of the Above
Section 15 Design
ANATOMY OF A SQL STATEMENT
If you want to see just a subset of the columns in a table, you use what symbol?
None of the above, instead of using a symbol you name the columns you want to see the data for.
The SQL SELECT statement is capable of:
Selection and projection
What is a NULL value?
An unknown value
SELECT * FROM departments; is a:
Statement
The order of operator precedence is
* / + -
RELATIONAL DATABASE TECHNOLOGY
The following statements are true regarding tables in a RDBMS:
A table holds all the data necessary about something in the real world, such as employees, invoices or customers.
Tables contain fields, which can be found at the intersection of a row and a column.
Once
data has been created in a RDBMS, the ony way of getting it out again
is by writing a Java or C program. No other languages can be used to
access that data. True or False?
False
The following table creation statement is valid. True or False?
CREATE TABLE country (ID NUMBER(6) NOT NULL, NAME VARCHAR2(30) NOT NULL, LOC VARCHAR2(40), REG_ID NUMBER, NAME VARCHAR2(25))
False
RDBMS stands for
Relational Database management system
Section 16 Design
WORKING WITH COLUMNS, CHARACTERS, AND ROWS
The concatenation operator ...
All of the above
In order to eliminate duplicate rows use the ________ keyword
DISTINCT
Which of the following is NOT BEING DONE in this SQL statement?
SELECT first_name || ' ' || last_name "Name"
FROM employees;
Concatenating first name, middle name and last name
The following is a valid SQL SELECT statement. True or False?
SELECT first_name || ' ' || last_name alias AS Employee_Name
FROM employees:
False
The structure of the table can be displayed with the _________ command:
Desc and Describe
LIMIT ROWS SELECTED
Which of the following are true?
Date values are enclosed in single quotation marks
Date values are format-sensitive
Which of the following is true?
Date values are enclosed in single quotation marks
How can you write not equal to in the WHERE-clause
All of the above
To restrict the rows returned from an SQL Query, you should use the _____ clause:
WHERE
Which of the following would be returned by this SELECT statement:
SELECT last_name, salary
FROM employees
WHERE salary < 3500;
LAST_NAME SALARY
Davies 3100
Which example would limit the number of rows returned?
SELECT title FROM d_songs WHERE type_code = 88;
Which of the following statements will work?
SELECT first_name ||' '||last_name NAME, department_id DEPARTMENT, salary*12 "ANNUAL SALARY"
FROM employees
WHERE last_name = 'King';
Which query would give the following result?
LAST_NAME FIRST_NAME DEPARTMENT_ID
King Steven 90
SELECT last_name, first_name, department_id
FROM employees
WHERE last_name = 'King';
COMPARISON OPERATORS
Which of the following WHERE clauses would not select the number 10?
WHERE hours <>10
Which two statements would select salaries that are greater than or equal to 2500 and less than or equal to 3500?
WHERE salary >= 2500 AND salary <= 3500
WHERE salary BETWEEN 2500 AND 3500
When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False?
True
Which of the following are examples of comparison operators used in the WHERE clause?
all of the above
Section 17 Design
LOGICAL COMPARISONS AND PRECEDENCE RULES
Find the clause that will give the same results as:
SELECT *
FROM d_cds
WHERE cd_id NOT IN(90, 91, 92);
WHERE cd_id != 90 and cd_id != 91 and cd_id != 92;
Which of the following are examples of logical operators that might be used in a WHERE clause.
AND, OR
NOT
What will be the results of the following selection?
SELECT *
FROM employees
WHERE last_name NOT LIKE 'A%' AND last_name NOT LIKE 'B%'
All last names that do not begin with A or B
Which of the following statements best describes the rules of precedence when using SQL?
The order in which the expressions are evaluated and calculated
Which of the following is earliest in the rules of precedence?
Arithmetic operator
Which of the following would be returned by this SQL statement:
SELECT First_name, last_name, department_id
FROM employees
WHERE department_id IN(50,80)
AND first_name LIKE 'C%'
OR last_name LIKE '%s%'
All of the above
Which symbol in the WHERE clause means "Not Equal To"?
NOT IN (...)
<>
SORTING ROWS
What clause must you place in a SQL statement to have your results sorted from highest to lowest salary?
ORDER BY salary DESC
A column alias can be specified in an ORDER BY Clause. True or False?
True
Which of the following is true of the ORDER BY clause:
Must be the last clause of the SQL statement
Defaults to an ascending order (ASC)
What columns can be added to the following SELECT statement in its ORDER BY clause? (Choose Three)
SELECT first_name, last_name, salary, hire_date
FROM employees
WHERE department_id = 50
ORDER BY ?????;
last_name, first_name
All columns in the EMPLOYEES table
Any column in the EMPLOYEES table, any expression in the SELECT list or any ALIAS in the SELECT list
INTRODUCTION TO FUNCTIONS - SINGLE ROW FUNCTIONS
The following statement represents a multi-row function. True or False?
SELECT MAX(salary)
FROM employees
True
The conversion function TO_CHAR is a single row function. True or False?
True
The following statement represents a multi-row function. True or False?
SELECT UPPER(last_name)
FROM employees;
False
The function COUNT is a single row function. True or False?
False
Will the following statement return one row?
SELECT MAX(salary), MIN(Salary), AVG(SALARY)
FROM employees;
Yes, it will return the highest salary, the lowest salary and the average salary from all employees
sumur : http://quizoracle.blogspot.co.id/
