True & False questions, ER Diagrams & SQL queries
CIS 430 CIS 311 Dr. Guo Part PKPart# Description Cost Employee PKEmployeeId Emp_name has Repair PKInvoice# Date Problem FK1EmployeeId FK2Serial# Part Used PK,FK2Part# PK,FK1Invoice# NumberUsed Equipment PKSerial# FK1EmployeeId Description Equip Cost Code Lab PK,FK1Serial# Date Purchased Art PK,FK1Serial# Color Type Office PK,FK1Serial# has Makes is assigned to Sent For O 6/12/21 Name__________________________________ Date____________________________ TRUE-FALSE Directions: Indicate whether each of the following statements is True or False. _____ 1. The conceptual schema is a description of precisely how the data in a database is stored in secondary memory or on a hardware device. _____ 2. In the Entity/Relationship model, a primary key cannot be composite. _____ 3. An instance of a strong entity can exist without having an instance of a weak entity. _____ 4. In the relationship below, Entity X would be considered the ‘Parent’ entity. _____ 5. If STUDENT was defined as an entity that has more than one attribute, Student_ID would be considered an instance of STUDENT. _____ 6. Although we see many atomic primary keys in associative entities, rarely do we run into an identifying relationship that utilizes composite primary keys. _____ 7. A derived attribute is also called an intelligent attribute. _____ 8. The SQL command used to populate tables is the INSERT command.. _____ 9. In SQL, null values are equivalent to zero. _____ 10. A repository is the structure that contains object descriptions created by DBMS. MULTIPLE CHOICE Directions: In the space provided, place the letter of the BEST selection for each of the following statements or questions. _____ 1. Assume that both Faculty and Office are entities. Also assume that all faculty members must be assigned to exactly one office. You would implement this business rule using a. cardinality constraints c. a domain constraint b. supertype/subtype constraints d. multi-valued attributes _____ 2. Assume each student has an attribute height that is measured at the beginning of semester. Height is measured in feet and inches. You would model the attribute as a(n) a. single-valued composite attribute c. multi-valued atomic attribute b. single-valued derived attribute d. Multi-valued derived attribute _____ 3. Assume there is a many-to-many relationship between Students and Classes. The relationship is implemented with an intersection (associative entity). If there are 50 Students, 10 Classes, and each student takes 6 classes, how many instances will there be in the intersection (associative entity)? a. 50 c. 300 b. 60 d. 3,000 _____ 4. Which statement of the following is correct? a. Group by and Order by are very similar. b. Having can only be used with Group By and Group by must be used with Having. c. In an identifying relationship, a strong table is always a parent table. d. In an identifying relationship, a weak table is not always a child table. _____ 5. Assume a student can take an SAT test more than once. Each SAT test has a Date, English Score and Math Score. SAT test would be considered a(n) a. derived atomic attribute c. multi-valued composite attribute b. multi-valued atomic attribute d. derived composite attribute _____ 6. In a parent and child relationship, in order to force a child to always have a parent (minimum cardinality of 1) you would a. make the foreign key in the child required b. make the primary key of the child required c. always make the relationship id-dependent d. make the foreign key in the parent required _____ 7. In DBMS, a constraint refers to a. who can process the data b. how the business data is processed c. a rule that cannot be violated by users d. the amount of memory available for the database _____ 8. For the relationship represented in the figure below, which of the following is true? I. An employee can work in more than one department but does not have to work for any department. II. A department must have at least one employee. III. A department may not have any employees. IV. An employee has to work for one and only one department a. I and II b. II and III c. III alone d. IV alone _____ 9. Which of the following is software used to create, maintain, and provide controlled access to databases? a. Network operating system b. User view c. Database management system (DBMS) d. Attribute _____ 10. What will be returned when the following SQL query is executed? Select driver#, count(*) as COUNTDELVR from deliveries group by driver# having count(*) > 2; a. A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries b. A listing of all drivers c. A listing of the number of deliveries greater than 2 d. A listing of all drivers who made more than 2 deliveries SHORT ANSWER AND PROBLEMS 1. Sketch the ERD for the following scenario: (Use proper ERD notations for attributes, entities, and relationships. Show all cardinalities, primary keys and foreign keys. Resolve all multi-valued attributes & M:N relationships properly.) · Clients have a unique Client_Id and they have a Name. Clients award Projects to the firm. Each Project is awarded by exactly one Client and a client can award more than one Project but doesn’t have to. No projects exist that are not awarded by a client. Projects have a unique Project_ID and they have attributes of Description and Due_Date. · Programmers are assigned to Projects and have a unique Programmer_Id. Programmers also have a Name, Office, and Language. A programmer must know at least one language and most programmers know more than one. Different programmers may know the same language. A Project may have no programmers assigned or a project may have several programmers assigned. A programmer must be assigned to at least one project but they may be assigned to several. · The programmers keep track of the time they work on the project using Time Cards. The time card records the Date and the Hours a programmer works on a project. A programmer creates a time card each time they work on a project. A programmer can work on a project on different days but s/he can only work on one project each day. · You do not need to explain cardinalities in words, but if you use any assumptions, make sure to indicate them in your answers. 2. Use the ER Diagram on the last page. Write each of the following queries. i. List the Equipment serial number. List each number only one time. ii. List the Serial# and Description of all Equipment that has NOT been assigned to any employee. Order the list by Serial#. iii. Assume that all parts have been discounted by 10%. List each Part#, Part Description, Cost, and Discounted_Price for all Parts that originally cost more than $100.00. iv. List each Date and the number of Repairs that were made on that date. v. List the ID of the employee that has been assigned the equipment with serial number 111. vi. Create the listing of all Repairs that have Invoice# greater than 1845. Sort the results by Equipment Serial# descending. Each row in the listing should read: Repair Invoice Number: Invoice# Problem is: Problem For example: Repair Invoice Number: 2738 Problem is: Missing Memory vii. List the repair invoice# that occurs before 2014-1-18 and has a problem with either “Circuit” or “Battery”. Andrew Arnold Entity Y Entity X d Total Specialization 6 _1347286202.vsd Table