Midterm Prep Weeks 1-7 -- CIST1220-Structured Query Language-SQL

Foreign keys cannot be null when A) It is part of a primary key B) It refers to another table C) It contains three or more columns

A) It is part of a primary key

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

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 B) False

A) True

Modeling historical data can produce a unique identifier that includes a date. True or False? A) True B) False

A) True

Oracle was one of the first relational database systems available commercially. True or False? A) True B) False

A) True

Relationships can be either mandatory or optional. True or False?

A) True

The demand for Information Technology professionals in today's market is increasing. A) True B) False

A) True

Two entities can have one or more relationships between them. True or False?

A) True

A foreign key always refers to a primary key in the same table. True or False?

B) False

Personal computers (PCs) have been in existence since 1950. True or False? A) True B) False

B) False

Relationships on an ERD can only be transformed into UIDs in the physical model? True or False?

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

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. A) No B) Yes

B) Yes

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. A) Unrealistic B) Identification C) Artificial D) Structured

C) Artificial

Column integrity refers to A) Columns always having values B) Columns always containing positive numbers C) Columns always containing values consistent with the defined data format D) Columns always containing text data less than 255 characters

C) Columns always containing values consistent with the defined data format

What are the major content areas covered in the Oracle Academy? A) SQL, database configuration, and performance tuning. B) Data Modeling, PJava and C+ C) Data Modeling, SQL, and PL/SQL D) Database programming, and Computer repair

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? A) System becomes 100% unstable; allows users to log on and log off at will. B) Reporting becomes nearly impossible; users enjoy this. C) Increased usability and flexibility of a system; e.g., we can the trace the different managers an employee had over time. D) Users are able to create complex programs in support of this component.

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 ___________. A) Decency B) Normalcy C) Normalization D) Replication

C) Normalization

If the same relationship is represented twice in an Entity Relationship Model, it is said to be: A) Resourceful B) Removable C) Redundant D) Replicated

C) Redundant

The transformation from an ER diagram to a physical design involves changing terminology. Entities in the ER diagram become __________ , and attributes become ____________. A) Foreign Keys, Columns B) Columns, Tables C) Tables, Columns D) Tables, Foreign Keys

C) Tables, Columns

Secondary UID's are A) Always comprised of numbers B) Mandatory in data modeling C) Useful as an alternative means of identifying instances of an entity D) Not permitted in data modeling

C) Useful as an alternative means of identifying instances of an entity

How would you model a business rule that states that on a student's birthday, he does not have to attend his classes? A) Use a supertype B) Use a subtype C) You cannot model this. You need to document it D) Make the attribute Birthdate mandatory

C) You cannot model this. You need to document it

A non-transferable relationship is represented by which of the following symbols? A) Heart B) Triangle C) Circle D) Diamond

D) Diamond

Which of the following are examples of data vs. information: A) Student age vs. average age of all students in class B) Bank deposit amount vs. total account balance C) Winning time for a race vs. length of race D) Price of a computer vs. total sales of all computers for a company E) A, B, and D

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? A) It facilitates discussion; a picture is worth a thousand words B) It forms important ideal system documentation C) It takes into account government regulations and laws D) It forms a sound basis for physical database design E) All of the above

E) All of the above It facilitates discussion; a picture is worth a thousand words It forms important ideal system documentation It takes into account government regulations and laws It forms a sound basis for physical database design

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

Many to many relationships between entities usually hide what? Mark for Review A) Another entity B) Another relationship C) Uniqueness D) More attributes

A) Another entity

A recursive relationship should not be part of a UID. 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

Non-transferable relationships can only be mandatory, not optional. True or False?

B) False

When you resolve a M:M by creating an intersection entity, this new entity will always inherit: A) The UID's from the entities in the original M:M. B) Nothing is inherited from the original entities and relationship. C) A relationship to each entity from the original M:M. D) The attributes of both related entities.

C) A relationship to each entity from the original M:M.

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: A) Both tables A and B get a new column and a Foreign Key. B) The table A C) The table B D) Nowhere, One-to-One relationships are not transformed.

C) The table B

When you add the concept of time to your data model, you are: A) Adding complexity to your model. B) Just changing the model, but this does not change the complexity of it. C) Simplifying your model. D) None of these answers.

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 B) All overdue payments will have an added 10 % late fee C) All products will have a selling price no less than 30 % greater than wholesale D) Buildings to be purchased by the business must be current with earthquake building code

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. B) Some non-UID attributes can be dependent on the entire UID. C) No non-UID attributes can be dependent on any part of the UID. D) None of the above

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 B) Creates a fixed time for all events in a data model. C) Represents entities as time in the data model. D) Gives a timestamp to all entities.

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 B) Build, Analyze, Design C) Design, Build, Analyze D) Analyze, Build, Design

A) Analyze, Design, Build

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. A) Entity integrity B) Referential integrity C) Column integrity D) User-defined integrity

A) Entity integrity

In a physical data model, a relationship is represented as a: A) Foreign Key B) Unique Identifier C) Column D) Primary Key

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 B) Foreign key constraints C) Intersection entity D) Primary key constraints

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. B) The rules are fixed and should be followed. C) Always model time; you can take it out later if it is not needed. D) You would first determine the existence of the concept of time and map it against the Greenwich Mean Time.

A) It depends on the functional needs of the system.

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 B) One to Many Optional C) One to Many Mandatory D) Many to Many Optional

A) Many to Many Mandatory

Which of the following would best be represented by an arc? A) STUDENT ( University, Trade School) B) STUDENT (graduating, non-graduating) C) STUDENT (senior, junior) D) STUDENT (will-attend-university, will-not-attend-university)

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 B) They are easily implemented in the ERD diagram C) The data modeler must focus on structural rules, because they are easily represented diagrammatically and eliminate other rules that involve extra procedures or programming D) Both A and C are true

A) They capture all of the needs, processes, and required functionality of the business

Which of the following is a logical constraint that could result from considering how time impacts the storage of data? A) End Date must be before the Start Date. B) An ASSIGNMENT may only refer to a COUNTRY that is valid at the Start Date of the ASSIGNMENT. C) Dates have value only with a time component. D) ASSIGNMENT periods can overlap causing the database to crash.

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 A) Creating new attributes. B) Barring the relationships to the original entities. C) Placing the UID attributes from the original entities into the intersection entity. D) None of the above.

B) Barring the relationships to the original entities.

A Matrix Diagram will help you with all of the following except: A) Naming Relationships B) Defining Instances of Entities C) Identifying Entities D) Verifying Relationships Between Entities

B) Defining Instances of Entities

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? A) True B) 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

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

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

The overall mission of the Oracle Corporation is to use the internet and fast processing servers to build its own network. A) True B) False

B) False

This diagram could also be expressed as a supertype/subtype construction. True or False?

B) False

One-to-Many Optional to Mandatory becomes a _______________ on the Master table. A) Mandatory Foreign Key B) Nothing (There are no new columns created on the Master table) C) Optional Foreign Key D) Primary Key

B) Nothing (There are no new columns created on the Master 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. A) Entity integrity B) Referential integrity C) Column integrity D) User-defined integrity

B) Referential integrity

Which of the following is true about supertypes and subtypes? A) Two subtypes from the same supertype have a one-to-one relationship between them B) Subtypes inherit the relationships and attributes of the supertype C) Subtypes may have no more than 2 levels of nesting D) Supertype and subtype entities must mutually exclude one another

B) Subtypes inherit the relationships and attributes of the supertype

In a physical data model, an entity becomes a/an _____________. A) Constraint B) Table C) Column D) Attribute

B) Table

Why would you want to model a time component when designing a system that lets people buy bars of gold? A) You would not want to model this; it is not important. B) The price of gold fluctuates and, to determine the current price, you need to know the time of purchase. C) Sales people must determine where the gold is coming from. D) The Government of your country might want to be 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: A) Recursive B) Transferable C) Committed D) Implicit

B) Transferable

In an Oracle database, why would the following table name not be allowed 'EMPLOYEE JOBS'? A) The database does not understand all capital letters B) You cannot have spaces between words in a table name C) JOBS is a reserved word D) EMPLOYEE is a reserved word

B) You cannot have spaces between words in a table name

If you are tracking employment dates for an employee, do you need to have an "End Date" attribute? A) No, not if the company likes the employee. B) No, because an end date is usually redundant. C) Yes, if the company wants to track multiple start dates and insure that multiple employment dates for an employee do not overlap. D) Yes, because you always need an end date when you have a start date.

C) Yes, if the company wants to track multiple start dates and insure that multiple employment dates for an employee do not overlap.

Which of the following is the best scenario for using supertype/subtype entities: A) A grocery store that gives customers a choice of plastic or paper bags B) An ice cream store that sells ice cream in sugar cones and regular cones C) A pet store that sells small animals that need different sized cages and different food D) A vehicle dealership that sells boats on trailers, cars, and trucks

D) A vehicle dealership that sells boats on trailers, cars, and trucks

Which of the following would be a logical constraint when modeling time for a country entity? A) Daily traffic patterns must be monitored to determine which countries are overcrowded. B) People have births and deaths in their countries that must be tracked by the system. C) If you are doing a system for France or Germany, you would need security clearance. D) Countries may change their names and/or borders over a period of time.

D) Countries may change their names and/or borders over a period of time.

Which of the following is the definition for Third Normal Form? A) All attributes are single valued B) All attributes are uniquely doubled and independent C) An attribute must be dependent upon the entity's entire UID D) No non-UID attribute can be dependent on another non-UID attribute

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

When are relationships unnecessary? A) When the relationships connect 2 entities and they each have distinct meanings B) When the information does not relate to the model C) When they have the same visual structure but different meaning D) When you can derive the relationship from other relationships in the model

D) When you can derive the relationship from other relationships in the model

In this simple diagram, what comprises the unique identifier for the student class entity? A) student id and class id B) student id, class id, and course id C) course id D) student id and course id

D) student id and course id