Section 1 Introduction -INTRODUCTION TO THE ORACLE ACADEMY What are the major content areas covered in the Oracle Academy? Data Modeling, SQL, and PL/SQL There is a big increase in demand for Information Technology professionals professionals in today's market. True or False? True
-DATA VS. INFORMATION Consider an example where an Oracle database works "behind the scenes" for common internet activity. Which of the following best describes a database transaction? A person searching an airline website to find all available available fares for a flight. flight. What are the results of having all your data in one central location? Improved performance Easier access to data Which of the following are examples of data vs. information. E. Both A and B How do you turn "data" into "information"? By querying it or accessing it -HISTORY OF THE DATABASE Which of the following is the correct order for the Database Development Process? Strategy, Analysis, Design, Build Data Modeling is the last stage in the development of a database. True or False? False Oracle was one of the first relational database systems available commercially? commercially? True or False? True -MAJOR TRANSFORMATIONS IN COMPUTING Which of the following are examples of e-businesses that use database software? Online clothing store Online book store Online personal shopping service Are all of the following following examples of how changes in computing have have affected day-to-day activities? activities? Yes 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 message. Today you can use your phone to send a text message. Yes The overall mission of the Oracle Corporation is to use the internet and fast servers to meet the needs of organizations by only storing data. False Users would use which of the following software to access essential business applications? GUI Interface Internet Browser Operating System
[1]
Personal computers (PCs) have been in existence since 1950. True or False? False In the grid computing model, resources are pooled together for efficiency. True or False? True
Section 2 What is data modeling? -CONCEPTUAL & PHYSICAL MODELS A customized hat is an example of the conceptual model or the physical model? Physical Model Many reasons exist for creting a conceptual model. Choose three appropriate reasons from the options below. They model functional and informational needs. They capture current and future data needs. They accurately describe what a physical model will contain. An Entity Relationship Diagram is an example of a Physical Model. True or False? False Which of the following are reasons we create conceptual models? All of the above
-ENTITIES, INSTANCES, ATTRIBUTES AND IDENTIFIERS Which of the following entities most likely contains invalid attributes? Entity: Car. Attributes: Owner Occupation, Owner Salary, Speed All of the following would be instances of the entity PERSON except which? Male What is the purpose of a Unique Identifier? To identify one unique instance of an entity, by using one or more attributes and/or relationships. A/an _________ is a piece of information that in some way describes an entity. It is a property of the entity and it quantifies, qualifies, classifies or specifies the entity. Attribute In a physical data model, an attribute is represented as a/an Column Unique Identifiers.... Distinguish one instance of an entity from all other instances of that entity Which of the following statements about attributes are true? They describe, qualify, quantify, classify, or specify an entity. They have a data type such as a number or character string. Entities are usually verbs. True or False? False Some of the following could be attributes of an ENTITY called PERSON. Select the incorrect attributes for PERSON. Freddy Wilson Priya Hansenna An entity may have which of the following? Instances The word "Volatile" means.... Changing constantly; unstable
[2]
In the following statements, find two examples of ENTITY: Instance. DAIRY PRODUCT: milk BOOK: Biography of Mahatma Gandhi
-ENTITY RELATIONSHIP MODELING AND ERDS A well structured ERD will show only some parts of the finished data model. You should never try to model the entire system in one diagram, no matter how small the diagram might be. True or False? False An Entity Relationship model is independent of the hardware or software used for implementation. True or False? True Which of the following statements are true about ERD's? A piece of information should only be found one place on an ERD. You should not model derivable data. The purpose of an ERD is to document the p roposed system and facilitate discussion and understanding of the requirements captured by the developer. True or False? True
Section 3 Entity Relationship DIagramming -IDENTIFYIND RELATIONSHIPS What are the three properties that every relationship should have? Name, optionality, degree In a business that sells computers, choose the best relationship name from CUSTOMER to ITEM (computer, in this case). Each CUSTOMER must be the buyer of one or more ITEMS. Relationships can be either mandatory or optional. True or False? True Relationships always exist between 2 entities (or one entity twice) -ER DIAGRAMMING CONVERNTIONS Attributes are written inside the entity to which they belong. True or False? True Entity names are always plural. True or False? False Consider the recommended drawing conventions for ERD's. Indicate which of the following accurately describes diagramming conventions for entities and attributes: The * means that an attribute is mandatory or required and the entity name should be singular On an ER diagram which symbol identifies an attribute as part of a unique identifier. # Entity boxes are drawn as Soft Boxes Which symbol is used to show that a particular attribute is optional? o -SPEAKING ERDISH AND DRAWING RELATIONSHIPS
[3]
Two entities can have one or more relationships between them. True or False? True After looking at the diagram, choose the sentence below that could be "read" from the existing relationship (even though you're missing relationship labels!) Each Student may participate in one or more Activities. When reading a relationship between 2 entities, the relationship is only read from left to right. True or False? False -MATRIX DIAGRAMS Matrix Diagrams helps verify you have identified all possible relationships between your existing entities. True or False? True A Matrix Diagram will help you with all of the following except: Defining Instances of Entities Creating a Matrix Diagram is mandatory when doing Data Modeling. True or False? False
Section 4 Supertypes, Subtypes, Business Rules -SUPERTYPES AND SUBTYPES Which of the following is the best scenario for using supertype/subtype entities: A vehicle dealership that sells cars, trucks and boats on trailers. The "Other" subtype is best used: As an extra subtype to ensure that all instances of subtypes are mutually exclusive and complete. By having an "Other" subtype, all instances of the Supertype will be of one subtype type. Which of the following is a TRUE statement about the diagram below? Every A is a B A supertype should have at least two subtypes. True or False? True All instances of the subtypes must be an instance of the supertype. True or False? True A subtype can have a relationship not shared by the supertype. True or False? True Which of the following is true about supertypes and subtypes? Subtypes inherit the relationships and attributes of the supertype. All instances of the supertype are also instances of one of the subtypes. True or False? True -DOCUMENTING BUSINESS RULES How would you model a business rule that states that on a student's birthday, they do not have to attend their classes? You cannot model this. You need to document it Why is it important to identify and document business rules? It allows you to create your data model and then check it for accuracy.
[4]
How should you handle constraints that cannot be modeled on an ER diagram? List them on a separate document to be handled programmatically 'Only managers can approve travel requests' is an example of which of the following? A procedural business rule. Business rules are important to data modelers because: A. They capture all of the needs, processes and required functionality of the business. A business rule such as "All accounts must be paid in full within 10 days of billing" is best enforced by: Hiring a programmer to create additional programming code to identify and report accounts past due. Which of the following is an example of a structural business rule? All employees must belong to at least one department.
Section 5 Working with Entity Relationships -RELATIONSHIP TRANSFERABILITY If a relationship can be moved between instances of the entities it connects, it is said to be: Transferrable A non-transferable relationship is represented by which of the following symbols? Diamond Non-transferable relationships can only be mandatory, not optional. True or False? True
-RELATIONSHIP TYPES Which of the following pairs of entities is most likely to be modeled as a M:M relationship? STUDENT and CLASS CAR and DRIVER When resolving an M:M relationship, the new relationships will always be __________ on the many side. Mandatory If the same relationship is represented twice in an Entity Relationship Model, it is said to be: Redundant Many to many relationships between entities usually hide what? Another entity 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" Many to Many Mandatory When are relationships unnecessary? When you can derive the relationship from other relationships in the model Which of the following are relationship types? Many to Many One to Many
-RESOLVING MANY-TO-MANY RELATIONSHIPS
[5]
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 If an intersection entity is formed that contains no attributes of its own, its uniqueness may be modeled by Barring the relationships to the original e ntities. When you resolve a M-M by creating an intersection entity, this new entity will always inherit: A relationship to each entity from the original M-M. Section 6 Unique Identifiers and Normalization -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 d escriptive "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
[6]
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 Arcs and Historical Data -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?
[7]
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 Iden tifier 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 Modeling Change -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 h ave 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 e nd date in your system, because they can change fundamentally over time, e.g. Yugoslavia. Modeling historical data produces efficient ways for a business to o perate 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
[8]
-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 d ata storage? An ASSIGNMENT may only refer to a COUNTRY that is valid at the Start Date of the ASSIGNMENT. Section 10 Drawing Convetion -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
[9]
[10]
