• Author / Uploaded
• Ciprian Sofronia

• Categories
• Base de datos relacional
• Tabla (base de datos)
• Bases de datos
• Gestión de datos
• Áreas de informática

Citation preview

Section 1 1. The first step in system development is to document the requirements. Why? Mark for Review (1) Points Wrong. A blueprint for the database design is not needed. We can just start coding straight away. It clarifies what a business wants to accomplish, and provides measures for deciding if the system delivers all that is required. (*) It allows application development to be conducted without having to consider database design. It keeps businesses honest. Correct 2. Oracle Database Software provides which of the following functionality? (Choose two) Mark for Review (1) Points (Choose all correct answers) Graphical User Interface (*) Internet Browser Server (*) Operating System Incorrect. Refer to Section 1 Lesson 4. 3. Consider your school library. It will have a database with transaction details of which student borrows which books. Is details of the total number of books out on loan in one given month Data or Information? Mark for Review (1) Points Data Information (*) Both

Neither Incorrect. Refer to Section 1 Lesson 2. 4. The main subject areas taught by the Oracle Academy are: Mark for Review (1) Points Computer Repairs Database performance tuning. Data Modeling, SQL and PL/SQL (*) Systems programming and computer architecture Correct

Section 2 5. An entity is instantiated as a ? Mark for Review (1) Points Experience Instance Table (*) None of the above Correct 6. Which of the following entities most likely contains valid attributes? (Choose two) Mark for Review (1) Points (Choose all correct answers) Entity: Home. Attributes: Number of Bedrooms, Owner, Address, Date Built (*) Entity: Pet. Attributes: Name, Birthdate, Owner (*)

Entity: Car. Attributes: Owner Occupation, Owner Salary, Speed Entity: Mother. Attributes: Name, Birthdate, Occupation, Salary Correct 7. Relationship names are usually verbs. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 2 Lesson 2. 8. All of the following could be attributes of an ENTITY called PERSON except one. Select the incorrect one: Mark for Review (1) Points Haircolor Weight Gender Natacha Hansen (*) Correct 9. Which of the following statements about relationships are true? (Choose Two) Mark for Review (1) Points (Choose all correct answers) They become foreign keys in the database. (*) They must be mandatory to be created in the database. They can be either mandatory or optional. (*) They must exist between two different Entities.

Correct 10. The Physical Model is derived from the Conceptual Model. True or False? Mark for Review (1) Points True (*) False Correct Section 2 11. Entity and Attribute names are usually Nouns. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 2 Lesson 3.

Section 3 12. Entity names are always singular. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 3 Lesson 2. 13. Two entities can ONLY have one relationship between them. True or False? Mark for Review (1) Points True False (*)

Incorrect. Refer to Section 3 Lesson 3. 14. Which of the following are valid relationship degrees? (Choose two) Mark for Review (1) Points (Choose all correct answers) 1:1 (*) 1:M (*) 1:O O:O Incorrect. Refer to Section 3 Lesson 1. 15. Relationships represents something of significance to the business. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 3 Lesson 1. 16. Matrix Diagrams are developed BEFORE the ERD. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 3 Lesson 4.

Section 4 17. A business rule such as "We only ship goods after customers have completely paid any outstanding balances on their account" is best enforced by: Mark for Review

(1) Points Making the payment attribute null. Making the payment attribute optional. We need to trust our customers, and we know they will pay some day. Hiring a programmer to create additional programming code to verify no goods are shipped until the account has been settled in full. (*) Incorrect. Refer to Section 4 Lesson 2. 18. A supertype can only have two subtypes and no more. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 4 Lesson 1. 19. Which of the following is true about subtypes? Mark for Review (1) Points One instance may belong to two subtypes of the same supertype. Subtypes must be mutually exclusive. (*) Subtypes must not be mutually exclusive. Subtype entities may not have relationships to the other subtype entities, only the supertype itself. Incorrect. Refer to Section 4 Lesson 1. 20. Which of the following are suitable Entity names? (Choose Two) Mark for Review (1) Points (Choose all correct answers) DOGS ANIMAL (*)

ANIMALS DOG (*) Incorrect. Refer to Section 4 Lesson 1.

Page 2 of 5 Section 4 21. All instances of a subtype may be an instance of the supertype but does not have to. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 4 Lesson 1. 22. The "Other" subtype is mandatory. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 4 Lesson 1.

Section 5 23. Relationships can be Redundant. True or False? Mark for Review (1) Points True (*) False

Incorrect. Refer to Section 5 Lesson 2. 24. How do you include a relationship as part of the UID for an entity? Mark for Review (1) Points By barring the relationship in question (*) By reporting it in an external document By including the UID from the parent entity as an attribute in the entity You cannot model that. Incorrect. Refer to Section 5 Lesson 2. 25. Which of the following pairs of entities is most likely to be modeled as a M:M relationship? Mark for Review (1) Points CAR and WHEEL TREE and BRANCH PERSON and FINGERPRINT TEACHER and SUBJECT AREA (*) Incorrect. Refer to Section 5 Lesson 2. 26. If two entities have two relationships between them, these relationships can be either _____________ or _____________ ? Mark for Review (1) Points Redundant or Required (*) Replicated or Required Resourced and Really Good Redundant and Replicated Incorrect. Refer to Section 5 Lesson 2.

27. Intersection Entities are at the Master end in the relationships between it and the original entities. So the original entities are details or children of the newly created intersection entity. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 5 Lesson 3. 28. When you resolve a M-M you simply re-draw the relationships between the two original entities, no new entities are created. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 5 Lesson 3. 29. Intersection Entities often have the relationships participating in the UID, so the relationships are often barred. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 5 Lesson 3. 30. Transferable relationships can only be mandatory, not optional. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 5 Lesson 1. Section 6

31. Until all attributes are single-valued, the database model is said to be: Mark for Review (1) Points Normalized Not Normalized (*) 1st Normal Form 2nd Normal Form Incorrect. Refer to Section 6 Lesson 2. 32. The is no limit to how may UID's an entity can have. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 6 Lesson 1. 33. The first UID for an entity is called the Primary UID, the second is called Secondary UID and so on. Mark for Review (1) Points Yes, this is the way UID's are named. (*) No, it is not possible to have more than one UID for an Entity. Yes, but then it stops. No entities can have more than two UID's. No, each Entity can only have one UID, the secondary one. Incorrect. Refer to Section 6 Lesson 1. 34. All instances of the subtypes must be an instance of the supertype. Mark for Review (1) Points True False

Incorrect. Refer to Section 6 Lesson 4. 35. When is an entity in 2nd Normal Form? Mark for Review (1) Points When all non-UID attributes are dependent upon the entire UID. (*) When no attritibutes are mutually independant and fully independent on the primary key. When no attritibutes are mutually independent and all are fully dependent on the primary key. None of the Above. Incorrect. Refer to Section 6 Lesson 3.

Section 7 36. A particular problem may be solved using either a Recursive Realtionship or a Hierachy, though not at the same time. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 7 Lesson 2. 37. Which of the following would best be represented by an arc? Mark for Review (1) Points TEACHER (Female, Bob) DELIVERY ADDRESS (Home, Office) (*) PARENT (Girl, Bob) STUDENT (Grade A student, Average Student) Incorrect. Refer to Section 7 Lesson 1.

38. All relationships participating in an arc must be mandatory. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 7 Lesson 1. 39. An arc can also be modelled as Supertype and Subtypes. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 7 Lesson 1. 40. Primary UIDs are: Mark for Review (1) Points Something that each Entity should have, but is not mandatory (*) Useful as an alternative means of identifying instances of an entity, independent of their datatype Mandatory in data modeling Always comprised of numbers Incorrect. Refer to Section 7 Lesson 1. Section 7 41. No parts of an UID are mandatory. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 7 Lesson 1.

42. If Audit trail attributes are added to an entity they must be in the entities they are auditing. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 7 Lesson 3.

Section 9 43. You are doing a data model for a computer sales company, where the price of postage is dependent on what day of the week 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? Mark for Review (1) Points 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. (*) Email current price to all employees whenever the prices change. Update the prices in the system, print out the current prices when they change and pin them on the company noticeboard Allow them to enter whatever ever delivery charge they want. Incorrect. Refer to Section 9 Lesson 2. 44. Which of the following is a logical constraint that could result from considering how time impacts an example of data storage? Mark for Review (1) Points An ASSIGNMENT may only refer to an EMPLOYEE with a valid employee record at the Start Date of the ASSIGNMENT. (*) EMPLOYEE periods can overlap causing the database to crash. End Date must be before the Start Date. Dates must be stored with Time.

Incorrect. Refer to Section 9 Lesson 2. 45. If a system includes the concept of time and it stores Start Dates, then End Dates becomes Mandatory. For each Start Date attribute you create, you MUST create an End Date attribute and it must be mandatory. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 9 Lesson 1. 46. When you add the concept of time to your data model, your model becomes more complex. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 9 Lesson 1.

Section 10 47. There are formal rules for how to draw ERD's, even though they are only guidelines, you should always try to follow them. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 11 Lesson 1. 48. There is no point in trying to group your entities together on your diagram according to volume. Making a diagram pretty is a waste of time. True or False? Mark for Review (1) Points

True False (*) Incorrect. Refer to Section 11 Lesson 1. 49. All datamodels must be transformed from specifiec to generic. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 10 Lesson 2. 50. A datamodel containing generic modelling techniques must not be mixed in with nongeneric models. The generic entities MUST be drawn on a diagram of their own. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 10 Lesson

Test: Mid Term Exam - Database Design

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer. Section 1 Lesson 1 1. Once you have learned how to write programs and build systems, you no longer need any input or involvement from any users, as you are perfectly capable of delivering the systems the business needs and wants. Mark for Review (1) Points True. All that perfect systems need are correct programs.

False. Business requirements can and will change. For instance new legal requirements may arise. (*) True. All users do is delay systems delivery with their forever changing minds and new requirements. True. Users never know what they want anyway, so building systems are best left to the professionals. Section 1 Lesson 2 2. Consider your school library. It will have a database with transaction details of which student borrows which books. Is a record of one student borrowing one book Data or Information? Mark for Review (1) Points Data (*) Information Both Neither

3. What is the difference between "information" and "data"? Mark for Review (1) Points Data is held and understood only by Users Information is held and understood only by users Information and data have no differences - they are two words for the same thing Data turns into information when it is stored in a database and accessed by systems and users (*)

Section 1 Lesson 3 4. The first step in system development is to document the requirements. Why? Mark for Review (1) Points Wrong. A blueprint for the database design is not needed. We can just start coding straight away.

It clarifies what a business wants to accomplish, and provides measures for deciding if the system delivers all that is required. (*) It allows application development to be conducted without having to consider database design. It keeps businesses honest.

Section 1 Lesson 4 5. Databases perform these functions.... Mark for Review (1) Points Running multiple applications on multiple client-servers Providing integrated software on fast processing servers Providing software running on a variety of platforms and configurations allowing companies a structured way to access and manage their data (*) They run purely as client-based software on personal computers

6. Which of the following are types of databases? Mark for Review (1) Points (Choose all correct answers) Hierarchical (*) Relational (*) SQL Network (*) Section 2 Lesson 2 (Answer all questions in this section) 7. An entity can have many Unique Identifiers. True or False? Mark for Review (1) Points

True (*) False 8. Which of the following statements about relationships are true? (Choose Two) Mark for Review (1) Points (Choose all correct answers) They become Foreign Keys in the database. (*) They Must be Mandatory to be created in the database. They can be either mandatory or optional. (*) They must exist between two different Entities.

9. The entity/relationship model is created before the physical database design model. True or False? Mark for Review (1) Points True (*) False

10. Primary Unique Identifiers... (Choose Two) Mark for Review (1) Points (Choose all correct answers) Are Required. The data model is not complete until all entities have a Primary UID. (*) Distinguish each instance of an entity from all others (*) Are not required You may create more than one Primary Unique Identifier for an entity

Page 1 of 5

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer. Section 2 Lesson 2 (Answer all questions in this section) 11. A/an _________ is a picture of all the information that will form the basis for the finished system. Mark for Review (1) Points ERD (*) Process Table Attribute

12. All of the following would be instances of the entity ANIMAL SPECIES except which? Mark for Review (1) Points Dog Bird Elephant Leaf (*)

13. An entity is instantiated as a ? Mark for Review (1) Points experience instance table (*) none of the above

Section 3 Lesson 1 (Answer all questions in this section) 14. One Relationship can be both mandatory and optional at different ends. True or False? Mark for Review (1) Points True (*) False

15. Relationships represents something of significance to the business? True or False? Mark for Review (1) Points True (*) False

Section 3 Lesson 2 (Answer all questions in this section) 16. Entity Boxes are drawn with 90 degree angles, also known as Hard Boxes. True or False? Mark for Review (1) Points True False (*) 17. The single end of a Relationship is called: Mark for Review (1) Points Single Toe (*) Big Toe Single Foot Crows Foot

Section 3 Lesson 3 (Answer all questions in this section) 18. When reading an ERD including Relationships you are said to be speaking: Mark for Review (1) Points Relationship-ish Gibberish ERDish (*) Entity-ish

Section 4 Lesson 1 (Answer all questions in this section) 19. Which of the following are suitable Entity names? (Choose Two) Mark for Review (1) Points (Choose all correct answers) DOGS ANIMAL (*) ANIMALS DOG (*) Incorrect. Refer to Section 4 20. All instances of a subtypes may be an instance of the supertype but does not have to. True or False? Mark for Review (1) Points

True False (*)

Section 4 Lesson 1 (Answer all questions in this section) 21. The "Other" subtype is mandatory. True or False? Mark for Review (1) Points True False (*)

22. When creating entities you must follow these rules: (Choose Two) Mark for Review (1) Points (Choose all correct answers) Name them in Plural Name them in Singular (*) Exclude Attributes Include Attributes (*)

23. A Supertype can have only one subtype. True or False? Mark for Review (1) Points True False (*)

Section 4 Lesson 2 (Answer all questions in this section)

24. "Only teachers may supervise exams" is an example of which of the following? Mark for Review (1) Points A procedural business rule (*) An attribute that should be stored on the PERSON entity A structural business rule An attribute that is not be inherited from the PERSON supertype and is unique to the TEACHER subtype

25. How would you model a business rule that states that girls and boys may not attend classes together? Mark for Review (1) Points Use a supertype Use two subtypes with relationships from class to student gender (*) Make the attribute Gender mandatory You cannot model this. You need to document it.

26. A business rule such as "We only ship goods after customers have completely paid any outstanding balances on their account" is best enforced by: Mark for Review (1) Points Making the payment attribute null. Making the payment attribute optional. We need to trust our customers, and we know they will pay some day. Hiring a programmer to create additional programming code to verify no goods are shipped until the account has been settled in full. (*) Incorrect. Refer to Section 4 27. Business rules are important to data modelers. True or False? Mark for Review (1) Points

True (*) False

Section 5 Lesson 1 (Answer all questions in this section) 28. Transferable relationships can only be mandatory, not optional. True or False? Mark for Review (1) Points True False (*)

Section 5 Lesson 2 (Answer all questions in this section) 29. Which of the following pairs of entities is most likely to be modeled as a M:M relationship? Mark for Review (1) Points CAR and WHEEL TREE and BRANCH PERSON and FINGERPRINT TEACHER and SUBJECT AREA (*)

30. How do you include a relationship as part of the UID for an entity? Mark for Review (1) Points By barring the relationship in question (*) By reporting it in an external document By including the UID from the parent entity as an attribute in the entity

You cannot model that. Page 3 of 5 Section 5 Lesson 2 (Answer all questions in this section) 31. Many to many relationships must be left in the Model. It is important to have them documented as M-M. True or False? Mark for Review (1) Points True False (*) 32. A relationship from an entity back to itself is called a ___________ relationship. Mark for Review (1) Points Mandatory Recursive (*) Many to Many Optional Transferrable

Section 6 Lesson 1 (Answer all questions in this section) 33. The is no limit to how may UID's an entity can have. True or False? Mark for Review (1) Points True (*) False

Section 6 Lesson 2 (Answer all questions in this section) 34. An entity without repeated values is said to be on 1st Normal Form. True or False? Mark for Review (1) Points True (*)

False

Section 6 Lesson 3 (Answer all questions in this section) 35. Would it be a good idea to model age as an attribute of STUDENT Mark for Review (1) Points Yes Maybe it could stop us having to calculate someones age every time we need it Sometimes No - it breaks the Normalization rules (*)

Section 7 Lesson 1 (Answer all questions in this section) 36. Consider the entity ADDRESS with the attributes: ADDRESS: # House Number * Street * Town * City * Year of Build o City Population This entity is NOT in 3rd Normal Form ("no non-UID attribute can be dependent on another non-UID attribute). True or False? Mark for Review (1) Points True (*) False

37. Arcs are used to visually represent _________ between two or more relationships in an ERD. Mark for Review (1) Points Sameness Differencies Exclusivity (*) An Arc is what Noah sailed on with all the animals.

38. Arcs are Mandatory in Data modelling. All ERD's must have at least one Arc. True or False? Mark for Review (1) Points True False (*)

39. Which of the following would best be represented by an arc? Mark for Review (1) Points TEACHER (Female, Bob) DELIVERY ADDRESS ( Home, Office) (*) PARENT (Girl, Bob) STUDENT (Grade A student, Average Student)

40. An arc can be modelled as Supertype and Subtypes. True or False? Mark for Review (1) Points True (*) False

Page 4 of 5 Section 7 Lesson 2 (Answer all questions in this section) 41. A relationship can be both Recursive and Hierarchal at the same time. True or False? Mark for Review (1) Points True False (*)

Section 7 Lesson 3 (Answer all questions in this section) 42. If Audit trail attributes are added to an entity they must be in the entities they are auditing. True or False? Mark for Review (1) Points True (*) False

Section 8 Lesson 1 (Answer all questions in this section) 43. Communication Skills, Team-working and Presentations skills are all important for Consultants. True or False? Mark for Review (1) Points True (*) False

Section 8 Lesson 2 (Answer all questions in this section) 44. Which of the following are NOT valid techniques for effective public speaking? (Choose Two) Mark for Review (1) Points (Choose all correct answers) Whispering (*) Using specialist language and technical abbreviations when communicating technical information (*) Being enthusiastic All of the Above

Section 10 Lesson 1 (Answer all questions in this section) 45. What do users of a system without the concept of time loose? Mark for Review (1) Points Journalling becomes much easier. Journalling becomes slightly harder. The ability to track data over time. (*) Nothing is lost if a system does not track time.

46. When you add the concept of time to your data model, your model becomes more complex. True or False? Mark for Review (1) Points True (*) False

47. Modeling historical data is Optional. True or False? Mark for Review (1) Points True (*) False

48. If a system includes the concept of time, and it stores Start Dates, then End Dates becomes Mandatory. For each Start Date attribute you create, you MUST create an End Date attribute and it must be mandatory. True or False? Mark for Review (1) Points True False (*)

Section 10 Lesson 2 (Answer all questions in this section) 49. You are doing a data model for a computer sales company, where the price of postage is dependant on what day of the week 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? Mark for Review (1) Points 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. (*) Email current price to all employees whenever the prices change. Update the prices in the system, print out the current prices when they change and pin them on the company noticeboard Allow them to enter whatever delivery charge they want.

50. All systems must include functionality to provide logging or journaling in conceptual data models. True or False? Mark for Review (1) Points True False (*)

sectiunea 17 1. Which of the following commands will display the last name concatenated with the job ID from the employees table, separated by a comma and space, and label the resulting column "Employee and Title"? Mark for Review (1) Points SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM employees; SELECT last_name||', '|| job_id "Employee and Title" FROM employees; (*) SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM emp; SELECT last_name||","|| job_id "Employee and Title" FROM employees;

2. You want to determine the orders that have been placed by customers who reside in Chicago. You write this partial SELECT statement: SELECT orderid, orderdate, total FROM orders; What should you include in your SELECT statement to achieve the desired results? Mark for Review (1) Points AND city = Chicago; AND city = 'Chicago'; WHERE city = 'Chicago'; (*) WHERE city = Chicago;

3. You need write a SELECT statement that should only return rows that contain 34, 46, or 48 for the DEPARTMENT_ID column. Which operator should you use in the WHERE clause to compare the DEPARTMENT_ID column to this specific list of values? Mark for Review (1) Points = != IN (*) BETWEEN..AND.. 4. You need to display all the employees whose last name starts with the letters Sm . Which WHERE clause should you use? Mark for Review (1) Points WHERE last_name LIKE 'Sm%' (*) WHERE last_name LIKE '%Sm' WHERE last_name LIKE '_Sm' WHERE last_name LIKE 'Sm_'

5. You want to retrieve a list of customers whose last names begin with the letters Fr . Which keyword should you include in the WHERE clause of your SELECT statement to achieve the desired result? Mark for Review (1) Points AND IN BETWEEN LIKE (*)

6. Which comparison condition would you use to select rows that match a character pattern? Mark for Review (1) Points IN

LIKE (*) ALMOST SIMILAR

7. You need to display only unique combinations of the LAST_NAME and MANAGER_ID columns in the EMPLOYEES table. Which keyword should you include in the SELECT clause? Mark for Review (1) Points ONLY UNIQUE DISTINCT (*) DISTINCTROW

8. You need to display employees whose salary is in the range of 10000 through 25000 for employees in department 50 . What does the WHERE clause look like? Mark for Review (1) Points WHERE department_id < 50 AND salary BETWEEN 10000 AND 25000 WHERE department_id > 50 AND salary BETWEEN 10000 AND 25000 WHERE department_id = 50 AND salary BETWEEN 25001 AND 10001 WHERE department_id = 50 AND salary BETWEEN 25000 AND 10000 (*)

9. Which symbol represents the not equal to condition? Mark for Review (1) Points #

'+' != (*) ~

10. When using the LIKE condition, which symbol represents any sequence of none, one or more characters? Mark for Review (1) Points _ % (*) # &

11. Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10? Mark for Review (1) Points WHERE (*) FROM SELECT IS

12. The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(9) PrimaryKey LAST_NAME VARCHAR2 (20) FIRST_NAME VARCHAR2 (20) DEPARTMENT_ID NUMBER(5) NOT NULL MANAGER_ID NUMBER(9) NOT NULL Evaluate these two SELECT statements: 1. SELECT DISTINCT employee_id, department_id, manager_id FROM employees; 2. SELECT employee_id, department_id, manager_id FROM employees; Which of the following statements is true? Mark for Review

(1) Points The two statements will display the same data. (*) The first statement will display a particular DEPARTMENT_ID only once. The first statement will NOT display values from all of the rows in the EMPLOYEES table The second statement could display a unique combination of the EMPLOYEE_ID, MANAGER_ID, and DEPARTMENT_ID values more than once.

13. You want to create a report that displays all employees who were hired before January 1, 2000 and whose annual salaries are greater than 50000. The EMPLOYEES table contains these columns: EMPLOYEE_ID VARCHAR2(5) PRIMARY KEY LAST_NAME VARCHAR2(35) HIREDATE DATE DEPARTMENT_ID NUMBER(4) The SALARY table contains these columns: SALARYID VARCHAR2(5) PRIMARY KEY SALARY NUMBER(5, 2) EMPLOYEE_ID VARCHAR2(5) FOREIGN KEY Which query should you issue? Mark for Review (1) Points SELECT last_name, hiredate, salary FROM employees NATURAL JOIN salary USING employee_id WHERE hiredate < 01-jan-00 AND salary > 50000; SELECT last_name, hiredate, salary FROM employees JOIN salary ON employee_id = employee_id WHERE hiredate < '01-jan-00' AND salary > 50000; SELECT last_name, hiredate, salary FROM employees NATURAL JOIN salary WHERE hiredate < '01-jan-00' AND salary > 50000; (*) SELECT last_name, hiredate, salary

FROM employees (+) salary WHERE hiredate < '01-jan-00' AND salary > 50000;

14. The STUDENT table contains these columns: STUDENT_ID NUMBER(10) Primary Key LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) MAIN_SUBJECT_ID NUMBER(3) ADVISOR_ID NUMBER(5) Evaluate this statement: SELECT DISTINCT advisor_id, main_subject_id FROM student; Which statement is true? Mark for Review (1) Points Each ADVISOR_ID can be displayed only once. Each MAIN_SUBJECT_ID can be displayed more than once per ADVISOR_ID. (*) Each combination of ADVISOR_ID and MAIN_SUBJECT_ID can be displayed more than once. Each MAIN_SUBJECT_ID can be displayed only once per query.

15. If you write queries using the BETWEEN operator it does not matter in what order you enter the values, i.e. BETWEEN low value AND high value will give the same result as BETWEEN high value and low value. True or False? Mark for Review (1) Points True False (*)

16. The EMPLOYEES table includes these columns: EMPLOYEE_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(15) NOT NULL FIRST_NAME VARCHAR2(10) NOT NULL HIRE_DATE DATE NOT NULL

You want to produce a report that provides the first names, last names and hire dates of those employees who were hired between March 1, 2000, and August 30, 2000. Which statements can you issue to accomplish this task? Mark for Review (1) Points SELECT last_name, first_name, hire_date FROM employees WHERE hire_date BETWEEN '01-MAR-00' AND '30-AUG-00'; (*) SELECT last_name, first_name, hire_date FROM employees WHERE hire_date BETWEEN '30-AUG-00' AND '01-MAR-00'; SELECT last_name, first_name, hire_date FROM employees GROUP BY hire_date >= '01-MAR-00' and hire_date = '01-MAR-00' and hire_date 20000; Which values would cause the logical condition to return TRUE? Mark for Review (1) Points DEPARTMENT_ID = 10 and SALARY = 20000 DEPARTMENT_ID = 20 and SALARY = 20000 DEPARTMENT_ID = null and SALARY = 20001 DEPARTMENT_ID = 10 and SALARY = 20001 (*)

22. Which of the following elements cannot be included in a WHERE clause? Mark for Review (1) Points A column alias (*) A column name A comparison condition A constant

23. What does the DISTINCT keyword do when it is used in a SELECT clause? Mark for Review

(1) Points Hides NULL values Eliminates all unique values and compares values Eliminates duplicate rows in the result (*) Eliminates only unique rows in the result

24. You need to display employees with salaries that are at least 30000 or higher. Which comparison operator should you use? Mark for Review (1) Points > "=>" >= (*) != 25. The PRODUCT table contains these columns: PRODUCT_ID NUMBER(9) DESCRIPTION VARCHAR2(20) COST NUMBER(5,2) LOCATION_ID VARCHAR2(10) You want to display product costs with these desired results: 1. The cost displayed for each product is increased by 10 percent. 2. The product location id must be 4859, 9789, or 9898. 3. Ten percent of the original cost is less than $10. Which statement should you issue? Mark for Review (1) Points SELECT product_id, cost * 1.10 FROM product WHERE cost * .10 < 10.00 AND location_id IN (4859, 9789, 9898); (*) SELECT product_id, cost * .10 FROM product WHERE cost * 1.10 > 10.00 AND location_id IN (4859, 9789, 9898);

SELECT product_id, cost * 1.10 FROM product WHERE cost * 1.10 < 10.00 AND location_id = (4859, 9789, 9898); SELECT product_id, cost * 1.10 FROM product WHERE cost * .10 > 10.00 AND location_id = (4859, 9789, 9898);

26. When using the LIKE condition to search for _ symbols, which character can you use as the default ESCAPE option? Mark for Review (1) Points % ^ & \ (*) 27. Which operator is used to combine columns of character strings to other columns? Mark for Review (1) Points * / + || (*)

28. Which statement best describes how column headings are displayed by default in Oracle Application Express: Mark for Review (1) Points Column headings are displayed left-justified and in lowercase. Column headings are displayed left-justified and in uppercase. Column headings are displayed centered and in uppercase. (*)

Column headings are displayed centered and in mixed case.

29. Where in a SQL statement can you not use arithmetic operators? Mark for Review (1) Points SELECT FROM (*) WHERE NONE

30. You need to display all the values in the EMAIL column that contains the underscore (_) character as part of that email address. The WHERE clause in your SELECT statement contains the LIKE operator. What must you include in the LIKE operator? Mark for Review (1) Points The ESCAPE option (\) and one or more percent signs (%) The (+) operator A percent sign (%) The ESCAPE option (\) (*)

31. Evaluate this SELECT statement: SELECT last_name, first_name, salary FROM employees; How will the heading for the SALARY column appear in the display by default in Oracle Application Express? Mark for Review (1) Points The heading will display with the first character capitalized and centered. The heading will display with the first character capitalized and left justified. The heading will display as uppercase and centered. (*) The heading will display as uppercase and left justified

32. If the EMPLOYEES table has the following columns, and you want to write a SELECT statement to return the employee last name and department number for employee number 176, which of the following SQL statements should you use? Name Type Length EMPLOYEE_ID NUMBER 22 FIRST_NAME VARCHAR2 20 LAST_NAME VARCHAR2 25 EMAIL VARCHAR2 25 PHONE_NUMBER VARCHAR2 20 SALARY NUMBER 22 COMMISSION_PCT NUMBER 22 MANAGER_ID NUMBER 22 DEPARTMENT_ID NUMBER 22 Mark for Review (1) Points SELECT last_name, department_id FROM employees WHERE employee_id = 176; (*) SELECT last_name, department_id FROM employees WHERE employee_id equals 176; SELECT first_name, employee_id FROM employees WHERE employee_id = 176; SELECT last_name, employee_id FROM employees WHERE employee_id equals 176;

33. You need to combine the FIRST_NAME and LAST_NAME columns in the EMPLOYEES table and display the columns as a combined character string. Which operator should you use? Mark for Review (1) Points + |

|| (*) AND

34. The Concatenation Operator does which of the following? Mark for Review (1) Points Links rows of data together inside the database. Links two or more columns or literals to form a single output column (*) Is represented by the asterisk (*) symbol Separates columns.

35. Which SELECT statement will display both unique and non-unique combinations of the MANAGER_ID and DEPARTMENT_ID values from the EMPLOYEES table? Mark for Review (1) Points SELECT manager_id, department_id DISTINCT FROM employees; SELECT manager_id, department_id FROM employees; (*) SELECT DISTINCT manager_id, department_id FROM employees; SELECT manager_id, DISTINCT department_id FROM employees;

36. You need to display all the rows in the EMPLOYEES table that contain a null value in the DEPARTMENT_ID column. Which comparison operator should you use? Mark for Review (1) Points "= NULL" NULL! ISNULL IS NULL (*) 37.

sectiunea 12 1. 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? Mark for Review (1) Points True False (*)

2. An "Arc Implementation" can be done just like any other Relationship - you simply add the required Foreign Keys. True or False? Mark for Review (1) Points True False (*)

3. The Oracle Database can implement a many to many relationship. You simply create two foreign keys between the two tables. True or False? Mark for Review (1) Points True False (*)

4. One-to-One relationships are transformed into Check Constraints in the tables created at either end of that relationship. True or False? Mark for Review (1) Points True False (*)

5. The explanation below is a column integrity constraint:

A column must contain only values consistent with the defined data format of the column. True or False? Mark for Review (1) Points True (*) False

6. The text below is an example of what constraint type: The value in the manager_id column of the EMPLOYEES table must match a value in the employee_id column in the EMPLOYEES table. Mark for Review (1) Points Entity integrity User-defined integrity Column integrity Referential integrity (*)

7. The text below is an example of what constraint type: If the number of BOOKS lent to a BORROWER in the LIBRARY exceeds 5, then we must send him/her a letter requesting the return of the BOOKS, which will require extra programming to enforce. Mark for Review (1) Points Entity integrity User-defined integrity (*) Column integrity Referential integrity

8. The explanation below is a User Defined integrity rule and must therefore be manually coded, the Database cannot enforce this rule automatically: A primary key must be unique, and no part of the primary key can be null. True or False?

Mark for Review (1) Points True False (*)

9. The transformation from an ER diagram to a physical design involves changing terminology. Primary Unique Identifiers in the ER diagram become __________ and relationships become ____________. Mark for Review (1) Points Foreign keys, Primary keys Primary keys, Foreign keys (*) Foreign keys, mandatory business rules Unique Keys, Primary keys

10. In a physical data model, a relationship is represented as a combination of: (Choose Two) Mark for Review (1) Points (Choose all correct answers) Column Primary Key or Unique Key (*) Check Constraint or Unique Key Foreign Key (*)

11. Why would this table name NOT work in an Oracle database? this_year_end+next_year Mark for Review (1) Points Table names must begin with an alphabetic character Too long The Plus sign + is not allowed in object names (*)

None of the above

12. 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? Mark for Review (1) Points True False (*) 13. When translating an arc relationship to a physical design, you must turn the arc relationships into foreign keys. Assuming you are implementing an Exclusive Design, you must also create two Unique Key Constraints to ensure the Arc is implemented correctly. True or False? Mark for Review (1) Points True False (*)

14. Foreign keys must be null. True or False? Mark for Review (1) Points True False (*) 15. A table must have a primary key. True or False? Mark for Review (1) Points True False (*) 17. If a primary key is a set of columns then one column must be null. True or False? Mark for Review (1) Points True False (*)

18. Attributes become tables in a database. True or False? Mark for Review (1) Points True False (*) 19. In a physical data model, an attribute becomes a _____________. Mark for Review (1) Points Table Foreign Key Constraint Column (*) 20. What do you create when you transform a many to many relationship from your ER diagram into a physical design? Mark for Review (1) Points Unique key constraints Intersection entity Intersection table (*) Two tables with Foreign key constraints between them 21. Entity integrity refers to Mark for Review (1) Points Tables always containing text data Tables always containing numeric data Columns having Primary Keys, Foreign Keys, Unique Keys and Check constraints defined in the database. Tables having Primary Keys, Foreign Keys, Unique Keys and Check constraints defined in the database. (*)

22. Identify all of the incorrect statements that complete this sentence: A primary key is: (Choose three) Mark for Review (1) Points (Choose all correct answers) A single column that uniquely identifies each column in a table (*) One or more columns in a table that uniquely identifies each row in that table A set of columns in one table that uniquely identifies each row in another table (*) Only one column that must be null (*) 23. Many to many relationships are implemented via a structure called a: ________________ Mark for Review (1) Points Supertype Intersection Table (*) Intersection Entity Subtype 24. In an Oracle database, why would 1_TABLE not work as a table name? Mark for Review (1) Points The database does not understand all capital letters There is no problem here. You can create a table called 1_TABLE. Object names must not start with a number. They must begin with a letter (*) TABLE is a reserved word 25. The transformation from an ER diagram to a physical design involves changing terminology. Secondary Unique Identifiers become Mark for Review (1) Points Columns Tables Unique Constraints (*)

Primary Key Constraints 26. Which of the following are reasons why you should consider using a Subtype Implementation? Mark for Review (1) Points The resulting table will reside in a single database and be used by just ONE user. When the common access paths for the supertypes are different. Business functionality and business rules, access paths and frequency of access are all very different between subtypes. (*) Most of the relationships are at the supertype level 27. A table must have at least one candidate key, as well as its primary key. True or False? Mark for Review (1) Points True False (*)

28. A foreign key cannot refer to a primary key in the same table. True or False? Mark for Review (1) Points True False (*) 29. It is possible to implement non-transferability via a simple Foreign Key Relationship. True or False? Mark for Review (1) Points True False (*) sectiunea 13 1. What command will return data from the database to you? Mark for Review

(1) Points FETCH GET SELECT (*) RETURN

2. What command can be used to create a new row in a table in the database? Mark for Review (1) Points CREATE NEW ADD INSERT (*) 3. The DESCRIBE command returns all rows from a table. True or False? Mark for Review (1) Points True False (*) 4. The _______ clause can be added to a SELECT statement to return a subset of the data. Mark for Review (1) Points ANYWHERE WHICH WHERE (*) EVERY 1. You cannot use computers unless you completely understand exactly how they work. True or False? Mark for Review (1) Points True

False (*)

2. All computers in the world speak the same languages, so you only need to learn one programming language - Oracle SQL. True or False? Mark for Review (1) Points True False (*)

3. When listing columns in the SELECT list, what should you use to separate the columns? Mark for Review (1) Points Commas (*) Semicolons Dashes Underscores

4. When you use the SELECT clause to list one or two columns only from a table and no WHERE clause, which SQL capability is used? Mark for Review (1) Points Joining only Selection only Projection only (*) Projection and Selection

5. Which statement best describes how arithmetic expressions are handled? Mark for Review (1) Points Addition operations are handled before any other operations. Multiplication and subtraction operations are handled before any other operations.

Multiplication and addition operations are handled before subtraction and division operations. Division and multiplication operations are handled before subtraction and addition operations. (*) 6. What would you use in the SELECT clause to return all the columns in the table? Mark for Review (1) Points An asterisk (*) (*) A minus sign (-) A plus sign (+) The ALL keyword

7. The EMPLOYEES table contains these columns: SALARY NUMBER(7,2) BONUS NUMBER(7,2) COMMISSION_PCT NUMBER(2,2) All three columns contain values greater than zero. There is one row of data in the table and the values are as follows: Salary = 500, Bonus = 50, Commission_pct = .5 Evaluate these two SQL statements: 1. SELECT salary + bonus + commission_pct * salary - bonus AS income FROM employees; 2. SELECT (salary + bonus ) + commission_pct * (salary - bonus) income FROM employees; What will be the result? Mark for Review (1) Points Statement 1 will return a higher value than statement 2. Statement 2 will return a higher value than statement 1. (*) Statement 1 will display a different column heading.

One of the statements will NOT execute.

8. The SELECT statement retrieves information from the database. In a SELECT statement, you can do all of the following EXCEPT: Mark for Review (1) Points Projection Manipulation (*) Joining Selection

9. If a SQL statement returns data from two or more tables, which SQL capability is being used? Mark for Review (1) Points Selection Projection Joining (*) Insertion 10. In a SELECT statement Additions are evaluated before Multiplications. True or False? Mark for Review (1) Points True False (*) 11. Evaluate this SELECT statement: SELECT (salary * raise_percent) raise FROM employees; If the RAISE_PERCENT column only contains null values, what will the statement return? Mark for Review (1) Points Only zeroes

Only null values (*) A null value or a zero depending on the value of the SALARY column A null value or a numeric value depending on the value of the SALARY column 12. Which SQL statement will return an error? Mark for Review (1) Points SEL * FR sky; (*) select star from sky; SELECT star FROM sky; SELECT * FROM sky; 13. In a SELECT clause, what is the result of 2 + 3 * 2? Mark for Review (1) Points 6 8 (*) 10 13 14. You want to create a list of all albums that have been produced by the company. The list should include the title of the album, the artist's name, and the date the album was released. The ALBUMS table includes the following columns: ALB_TITLE VARCHAR2(150) NOT NULL ALB_ARTIST VARCHAR2(150) NOT NULL ALB_DATE DATE NOT NULL Which statement can you use to retrieve the necessary information? Mark for Review (1) Points SELECT * FROM albums; (*) SELECT alb_title, alb_artist, alb_dates

FROM album; SELECT alb_title, alb_artist, alb_dates FROM albums; SELECT alb_title; alb_artist; alb_date FROM albums; 15. In which clause of a SELECT statement would you specify the name of the table or tables being queried? Mark for Review (1) Points The FROM clause (*) The SELECT clause The WHERE clause Any of the above options, you can list tables wherever you want to in a SELECT statement 16. In the default order of precedence, which operator would be evaluated first? Mark for Review (1) Points Subtractions Multiplications (*) Additions Divisions 17. You query the database with this SQL statement: SELECT * FROM students; Why would you use this statement? Mark for Review (1) Points To insert data To view data (*) To display the table structure

To delete data 18. There is only one kind of software used by all computers. True or False? Mark for Review (1) Points True False (*) 19. In a SQL statement, which clause specifies one or more columns to be returned by the query? Mark for Review (1) Points SELECT (*) FROM WHERE Any of the above options, you can list columns wherever you want to in a SELECT statement. 20. Which SQL keyword specifies that an alias will be substituted for a column name in the output of a SQL query? Mark for Review (1) Points AS (*) OR AND SUBSTITUTE 21. You query the database with this SQL statement: SELECT * FROM transaction WHERE product_id = 4569; Which SQL SELECT statement capabilities are achieved when this statement is executed? Mark for Review (1) Points Selection only (*)

Projection only Selection and projection only Projection, selection and joining sectiunea 18 1. Evaluate this SELECT statement: SELECT last_name, first_name, email FROM employees ORDER BY email; If the EMAIL column contains null values, which statement is true? Mark for Review (1) Points Null email values will be displayed first in the result. Null email values will be displayed last in the result. (*) Null email values will not be displayed in the result. The result will not be sorted.

2. What value will the following SQL statement return? SELECT employee_id FROM employees WHERE employee_id BETWEEN 100 AND 150 OR employee_id IN(119, 175, 205) AND (employee_id BETWEEN 150 AND 200); Mark for Review (1) Points 19 No rows will be returned 100, 101, 102, 103, 104, 107, 124, 141, 142, 143, 144, 149 (*) 200, 201, 202, 203, 204, 205, 206

3. Evaluate this SQL statement: SELECT product_id, product_name, price FROM products ORDER BY product_name, price; What occurs when the statement is executed? Mark for Review (1) Points The results are sorted numerically only. The results are sorted alphabetically only. The results are sorted numerically and then alphabetically. The results are sorted alphabetically and then numerically. (*)

4. Evaluate this SELECT statement: SELECT * FROM employees WHERE salary > 30000 AND department_id = 10 OR email IS NOT NULL; Which statement is true? Mark for Review (1) Points The OR condition will be evaluated before the AND condition. The AND condition will be evaluated before the OR condition. (*) The OR and AND conditions have the same precedence and will be evaluated from left to right The OR and AND conditions have the same precedence and will be evaluated from right to left

5. Which SELECT statement should you use to limit the display of product information to those products with a price of less than 50? Mark for Review (1) Points SELECT product_id, product_name

FROM products WHERE price < 50; (*) SELECT product_id, product_name FROM products HAVING price < 50; SELECT product_id, product_name FROM products WHERE price