Final Prep Q/A CIST1220-Structured Query Language-SQL
When you add the concept of time to your data model, you are:
A) Adding complexity to your model.
Which of the following is an example of a structural business rule?
A) All employees must belong to at least one department
What is the rule of Second Normal Form?
A) All non-UID attributes must be dependent upon the entire UID.
What is the function of logging or journaling in conceptual data models?
A) Allows you to track the history of attribute values, relationships, and/or entire entities
Which of the following represents the correct sequence of steps in the Database Development Process?
A) Analyze, Design, Build
Many to many relationships between entities usually hide what?
A) Another entity
In a physical data model, a relationship is represented as a:
A) Foreign Key
What do you create when you transform a Many-to-Many relationship from your ER diagram into a physical design?
A) Intersection table
How do you know when to use the different types of time in your design?
A) It depends on the functional needs of the system.
Foreign keys cannot be null when
A) It is part of a primary key
What uncommon relationship is described by the statements: "Each LINE must consist of many POINTS and each POINT must be a part of many LINES"
A) Many to Many Mandatory
Which of the following would best be represented by an arc?
A) STUDENT ( University, Trade School)
Business rules are important to data modelers because:
A) They capture all of the needs, processes, and required functionality of the business
A column represents a field in the physical database table.
A) True
A recursive relationship should not be part of a UID. True or False?
A) True
A table does not have to have a primary key. True or False?
A) True
All attributes that are part of the UID are mandatory. True or False?
A) True
All instances of a subtype must be an instance of the supertype. True or False?
A) True
All instances of the supertype are also instances of one of the subtypes. True or False
A) True
As a database designer, you have a responsibility to store data in only one place and in the best possible place. True or False?
A) True
If subtypes are listed, a supertype should have at least two subtypes. True or False?
A) True
In an ERD, it is a good idea to group your entities according to the expected volumes. By grouping high volume entities together, the diagrams could become easier to read. True or False?
A) True
In the grid computing model, resources are pooled together for efficiency. True or False?
A) True
Modeling historical data can produce a unique identifier that includes a date. True or False?
A) True
Oracle was one of the first relational database systems available commercially. True or False?
A) True
Relationships can be either mandatory or optional. True or False?
A) True
SELECT *FROM employees;This statement will retrieve all the rows in the employees table. True or False?
A) True
The demand for Information Technology professionals in today's market is increasing.
A) True
The simplest approach to remove a partial dependency is to use each portion of the primary key to create separate tables.
A) True
Two entities can have one or more relationships between them. True or False?
A) True
Two tables can be linked or joined together through a common field.
A) True
A field in the logical design of a database is represented by what in the physical database?
A) column
In an E-R Model a person, place, or thing with characteristics to be stored in the database are referred to as?
A) entity
Which of the following is a logical constraint that could result from considering how time impacts the storage of data?
B) An ASSIGNMENT may only refer to a COUNTRY that is valid at the Start Date of the ASSIGNMENT.
If an intersection entity is formed that contains no attributes of its own, its uniqueness may be modeled by
B) Barring the relationships to the original entities.
A Matrix Diagram will help you with all of the following except:
B) Defining Instances of Entities
Unique Identifiers:
B) Distinguish one instance of an entity from all other instances of that entity
A database is a physical storage device for data.
B) False
A foreign key always refers to a primary key in the same table. True or False?
B) False
A unique identifier can only be made up of one attribute. True or False?
B) False
An entity can be on 2nd Normal Form even if it has repeated values. True or False?
B) False
An entity can only have one UID. True or False?
B) False
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?
B) False
Creating a Matrix Diagram is a mandatory step in Data Modeling. True or False?
B) False
Data Modeling is the last step in the database development process. True or False?
B) False
Data is in second normal form (2NF) if it contains no repeating groups and has a primary key to uniquely identify each record.
B) False
Data redundancy is created through a process known as normalization.
B) False
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?
B) False
No database in the world is ever truly on 3rd Normal Form. Everyone always stops after 2nd Normal Form. True or False?
B) False
No formal rules exist for drawing ERD's. The most important thing is to make sure that all entities, attributes, and relationships are documented on the diagram. The Layout is insignificant. True or False?
B) False
Non-transferable relationships can only be mandatory, not optional. True or False?
B) False
One or more columns in a primary key can be null. True or False?
B) False
One-to-One relationships are transformed into Foreign Keys in the tables created at either end of that relationship. True or False?
B) False
Personal computers (PCs) have been in existence since 1950. True or False?
B) False
Relationships on an ERD can only be transformed into UIDs in the physical model? True or False?
B) False
The DIST or UNIQ keywords can be used to suppress duplicate data in the results of a SELECT statement.
B) False
The following entity is in 1st normal form: True or False? ENTITY: VEHICLE ATTRIBUTES: REGISTRATION MAKE MODEL COLOR DRIVER PASSENGER 1 PASSENGER 2 PASSENGER 3
B) False
The overall mission of the Oracle Corporation is to use the internet and fast processing servers to build its own network.
B) False
This diagram could also be expressed as a supertype/subtype construction. True or False?
B) False
When reading a relationship between 2 entities, the relationship is only read from left to right. True or False?
B) False
One-to-Many Optional to Mandatory becomes a _______________ on the Master table.
B) Nothing (There are no new columns created on the Master table)
A customized hat sitting on your friend's head is an example of which model--the conceptual model or the physical model?
B) Physical Model
Which of the following is true about supertypes and subtypes?
B) Subtypes inherit the relationships and attributes of the supertype
In a physical data model, an entity becomes a/an _____________.
B) Table
Why would you want to model a time component when designing a system that lets people buy bars of gold? notified of this transaction.
B) The price of gold fluctuates and, to determine the current price, you need to know the time of purchase.
If a relationship can be moved between instances of the entities it connects, it is said to be:
B) Transferable
Changes in computing have affected many of our day-to-day activities. Are all of the following activities examples of this change? Yes or No? In the past you used to use the phone system to call directory assistance to get a phone number. Today you can use your PC to look up a phone number online. In the past you used to have to go to the shoe store to buy shoes. Today you can use your PC to order shoes online. In the past you had to use your PC to send a person an email. Today you can use your phone to send a text message.
B) Yes
In an Oracle database, why would the following table name not be allowed 'EMPLOYEE JOBS'?
B) You cannot have spaces between words in a table name
Analyzing historical sales data stored in a database is commonly referred to as ____.
B) data mining
A foreign key is usually found on which side of a relationship?
B) many
Which of the following terms represents a collection of fields?
B) record
When you resolve a M:M by creating an intersection entity, this new entity will always inherit:
C) A relationship to each entity from the original M:M.
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". To be able to uniquely and efficiently identify one instance of the entity SHOE, a/an ______________ UID can be created.
C) Artificial
How do you turn "data" into "information"?
C) By querying it or accessing it
Column integrity refers to
C) Columns always containing values consistent with the defined data format
What are the major content areas covered in the Oracle Academy?
C) Data Modeling, SQL, and PL/SQL
What is the benefit to the users of a system that includes "time"; e.g. Start Date and End Date for Employees?
C) Increased usability and flexibility of a system; e.g., we can the trace the different managers an employee had over time.
When data is stored in more than one place in a database, the database violates the rules of ___________.
C) Normalization
If the same relationship is represented twice in an Entity Relationship Model, it is said to be:
C) Redundant
The transformation from an ER diagram to a physical design involves changing terminology. Entities in the ER diagram become __________ , and attributes become ____________.
C) Tables, Columns
Secondary UID's are
C) Useful as an alternative means of identifying instances of an entity
If you are tracking employment dates for an employee, do you need to have an "End Date" attribute?
C) Yes, if the company wants to track multiple start dates and insure that multiple employment dates for an employee do not overlap.
How would you model a business rule that states that on a student's birthday, he does not have to attend his classes?
C) You cannot model this. You need to document it
Which of the following may contain transitive dependencies, but not partial dependencies?
C) second normal form (2NF)
Relationships always exist between
D) 2 entities (or one entity and itself)
Which of the following is the best scenario for using supertype/subtype entities:
D) A vehicle dealership that sells boats on trailers, cars, and trucks
The data model can be used to __________.
D) All of the Above
Which of the following would be a logical constraint when modeling time for a country entity?
D) Countries may change their names and/or borders over a period of time.
A non-transferable relationship is represented by which of the following symbols?
D) Diamond
Which of the following is the definition for Third Normal Form?
D) No non-UID attribute can be dependent on another non-UID attribute
Examine the following entity and decide which attribute breaks the 2nd Normal Form rule: ENTITY: CLASS ATTRIBUTES: A) CLASS ID B) DURATION C) SUBJECT D) TEACHER NAME AND ADDRESS
D) TEACHER NAME AND ADDRESS
During which phases of the System Development Life Cycle would you roll out the system to the users?
D) Transition and Production
The explanation below is an example of which 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.
D) User-defined integrity
When are relationships unnecessary?
D) When you can derive the relationship from other relationships in the model
A ____ is a group of interrelated files.
D) database
If you want to see just a subset of the columns in a table, you use what symbol?
D) instead of using a symbol, you name the columns for which you want to see data.
In this simple diagram, what comprises the unique identifier for the student class entity?
D) student id and course id
Which of the following are examples of data vs. information:
E) A, B, and D A) Student age vs. average age of all students in class B) Bank deposit amount vs. total account balance D) Price of a computer vs. total sales of all computers for a company
Which of the following are reasons we create conceptual models?
E) All of the above
Many-to-Many relationships are perfectly acceptable in a finished ERD. There is no need to do any more work on them. True or False?
False
