null

CP2404CP5633_SampleExamQuestions.pdf

Home >Computer Science homework help >null

(Sample Questions)

Section A: Multiple Choice XX marks Question 1 [1 mark] The _________ serve(s) as the intermediary between the user and the database.

A) DBMS B) metadata C) end-user data D) programming language Question 2 [1 mark] A(n) ________ database is designed to support a company’s day-to-day operations.

A) desktop B) workgroup C) enterprise D) transactional Question 3 [1 mark] _________ exists when different and conflicting versions of the same data appear in different places.

A) Data independence B) Data inconsistency C) Data redundancy D) Data integrity Question 4 [1 mark] Which of the following is NOT TRUE about a relational DB or DBMS?

A) A relationship is an association between attributes. B) The attributes of an entity become the colums in the table. C) In a relational database, each entity has its own table. D) Programs created with Visual Basic, Java, Perl, PHP, or C++ have to access a

database through the DBMS. Question 5 [1 mark] Which of the following is TRUE about business rules?

A) They allow the designer to set company policies with regard to data. B) They allow the designer to develop business processes. C) They can serve as a communication tool between the users and designers. D) They provide a framework for the company’s self actualization. Question 6 [1 mark] Which of the following is TRUE about the relational database model?

(Sample Questions)

A) The order of the rows and columns is important to the DBMS. B) A foreign key must exist in both tables that have a relationship. C) Tables within a database share common attributes that enable the tables to be linked

together. D) To maintain referential integrity when linking multiple tables, a primary key must

reference another primary key.

Question 7 [1 mark] “Union-compatible” means that _______ .

A) names of the relation attributes can be different, but the data types must be identical B) names of the relation attributes must be the same, but the data types can be different C) names of the relation attributes must be the same and their data types must be

identical D) number of attributes must be the same, but the names and data types can be different Question 8 [1 mark] A(n) ____ key can be described a superkey without unnecessary attributes, that is, a minimal superkey.

A) index B) candidate C) foreign D) secondary Question 9 [1 mark] ________ might be represented with a multivalued attribute.

A) A person’s name B) An employee’s educational background C) A bank account balance D) A book title Question 10 [1 mark] Which of the following is TRUE about ER modelling?

A) The ERD represents the physical database as viewed by the database developer. B) When the PK of one entity does not contain the PK of a related entity, the relationship

is weak C) A ternary relationship exists when an association is maintained within a single entity. D) A derived attribute must be stored physically within the database. Question 11 [1 mark] Which of the following is NOT TRUE about a supertype-subtype relationship.

A) The entity supertype contains the common characteristics and the entity subtypes contain the unique characteristics of each entity subtype.

B) An entity supertype can have disjoint or overlapping entity subtypes

(Sample Questions)

C) Subtype entities inherit the relationships in which the supertype entity participates. D) At the implementation level, the supertype and its subtype(s) depicted in the

specialization hierarchy maintain a 1:M relationship Question 12 [1 mark] Which of the following is a reason why a set of normalised tables in a relational database may be denormalised?

A) data integrity B) query response time C) proper concurrency control D) security Question 13 [1 mark] Normalisation is a process for assigning ________ to entities.

A) data B) files C) attributes D) relations Question 14 [1 mark] Which design strategy would be best used to create a database solution for a large multinational company with data needs in multiple locations?

A) Bottom up design B) Decentralized design C) Centralized design D) Top Down design Question 15 [1 mark] Which of the following is NOT TRUE about database design phases of the database development lifecycle (DBLC)?

A) The first step in the DBLC is database initial study. B) Creating the conceptual design and selecting DBMS software are part of the database

design phase of the DBLC. C) The implementation and loading phase of the DBLC involves installing the DBMS. D) Producing the required information flow is part of the testing and evaluation phase

of the DBLC. Question 16 [1 mark]

(Sample Questions)

The _________ command within relational algebra (operation) takes a horizontal subset of a table.

A) SELECT B) DELETE C) PROGRAM D) PROJECT

Question 17 [1 mark] A join that includes non-matched rows in the output is called _________.

A) a theta join. B) an equi join. C) a natural join. D) an outer join. Question 18 [1 mark] The ______ clause is used to produce a list of only values that are different from one another.

A) AGGREGATE B) RESTRICTED C) SELECT D) DISTINCT

(Sample Questions)

Section B: Miscellaneous Questions XX marks 1. Use the table (sample Salesperson/Product records) shown below to answer the

questions (A) to (D) Salesperson

Number Salesperson

Name Product Number

Commission Percentage

Year of Hire

Department Number

Manager Name

Product name

Unit Price Quantity

137 Baker 19440 10 1995 73 Scott Hammer 17.50 473 186 Adams 24013 15 2001 59 Lopez Saw 26.25 3071 361 Carlyle 21765 25 2001 73 Scott Drill 32.99 3110 186 Adams 16386 15 2001 59 Lopez Wrench 12.95 1745 361 Carlyle 26722 20 2001 73 Scott Pliers 11.50 2738 186 Adams 21765 17.5 2001 59 Lopez Drill 32.99 1962 204 Dickens 21765 10 1998 73 Scott Drill 32.99 809 137 Baker 24013 12.5 1995 73 Scott Saw 26.25 170 204 Dickens 26722 12.5 1998 73 Scott Pliers 11.50 734 186 Adams 19440 17.5 2001 59 Lopez Hammer 17.50 2529 137 Baker 26722 10 1995 73 Scott Pliers 11.50 688 361 Carlyle 16386 20 2001 73 Scott Wrench 12.95 3729

(A) Fill in the following diagram to draw the dependency diagram of the relation presented in the

table above. Identify the primary key(s), draw all dependencies and label them properly (e.g. partial, transitive). (4.5 marks)

Sales person

Number

Sales person Name

Product Number

Commission Percentage

Year of Hire

Department Number

Manager Name

Product Name

Unit Price Quantity

[Sample Answer and marking scheme for example]

Sales person

Number

Sales person Name

Product Number

Commission Percentage

Year of Hire

Department Number

Manager Name

Product Name

Unit Price Quantity

Correct primary key: 1 mark Identifying all dependencies from the primary key to other attributes: 0.5 mark Two partial dependencies (1mark each) Transitive dependency (1 mark)

(Sample Questions)

(B) What normal form is the relation currently in? State the reasons for your answer. (2 marks)

Answer: 1NF (1 mark) – because it has a proper primary key (0.5 mark) and it has other dependencies like partial or transitive dependencies (0.5 mark) (C) Normalize the table to third normal form (3NF) and draw the dependency diagrams

of the 3NF tables. (6 marks) Answer: 1NF (1 mark) – because it has a proper primary key (0.5 mark) and it has other dependencies like partial or transitive dependencies (0.5 mark) <Product>

Product Number Product Name Unit Price

< Salesperson>

Salesperso n Number

Salesperson Name

Year of Hire

Departme nt Number

Department Number

Manager Name

<Salesperson-Product>

Salesperso n Number

Product Numbe

Commissio n

Percentage

Quantit y

1 mark for each correct table and 0.5 mark for each correct primary key

(Sample Questions)

(D) Using the results in , draw an entity relationship diagram (ERD). You need to use Crow’s foot notation. Your diagram should show all relevant attributes including primary and foreign keys. Indicate all connectivities, cardinalities and optionalities. (You may need to set up optionalities by your own assumptions. List all assumptions you made and every assumption should be correctly corresponded with your ERD.) (13.5 marks)

Answer:

Assumptions: 1) There may be some products which has not been sold before. 2) Some salespersons may not have any record of sales (maybe trainee?) 3) Each salesperson must be registered in (working for) one department 4) Some departments

may not have any salesperson

Marking Scheme:

- Correct transform from each table (resulted in Q13) – 1.5 marks for each table (including 0.5 mark for appropriate table name, 0.5 mark for correct primary key, 0.5 mark for correct non-primary key attributes) – [total 6 marks]

- Correct setting for foreign keys - 0.5 for each foreign key – [total 1.5 marks] - Correct relationships (only check connectivities and cardinalities) – 1 mark for

each relationship (0.5 mark – connection between correct entities, 0.5 mark – correct cardinality (1:M in the correct order) - [total 3 marks]

- Optionalities corresponding to assumptions (0.5 marks for each set of an optionality and corresponding assumption – give only partial marks if optionality is presented but no corresponding assumption was made) --- assumptions should be correctly corresponded to optionalities presented in ERD (Note: the mandatory optionalities caused by the strong relationships with bridge table (Salesperson-Product) should not be made by assumptions – thus only four assumptions should be made by students) – [total 3 marks]

(Sample Questions)

2. Consider a database which contains the following tables and sample data. This database is kept for a company who sells various house products.

Customer Customer

Num Customer

Name City Balance Credit Limit

Rep Num

148 Alice Fillmore $6,550 $7,500 20 282 Brooken Grove $431 $10,000 35 356 Ferguson Northfield $5,785 $7,500 65 408 Eshop Crystal $5,285 $5,000 35 462 Bgalore Grove $3,412 $10,000 65 524 Kline Fillmore $12,762 $15,000 20 608 Johnson Sheldon $2,106 $10,000 65 687 Lee Altonville $2,851 $5,000 35 725 Deerfield Sheldon $248 $7,500 35 842 Allsen Grove $8,221 $7,500 20

OrderLine Order Num

Part Num

Num Ordered

Quoted Price

21608 AT94 11 $21.95 21610 DR93 1 $495.00 21610 DW11 1 $399.99 21613 KL62 4 $329.95 21614 KT03 2 $595.00 21617 BV06 2 $794.95 21617 CD52 4 $150.00 21619 DR93 1 $495.00 21623 KV29 2 $1,290.0

Part Part Num Description

On Hand Class

Ware house Price

AT94 Iron 50 HW 3 $24 BV06 Home Gym 45 SG 2 $794 CD52 Microwave Oven 32 AP 1 $165 DL71 Cordless Drill 21 HW 3 $129 DR93 Gas Range 8 AP 2 $495 DW11 Washer 12 AP 3 $399 FD21 Stand Mixer 22 HW 3 $159 KL62 Dryer 12 AP 1 $349 KT03 Dishwasher 8 AP 3 $595 KV29 Treadmill 9 SG 2 $1,390

Orders Order Num OrderDate

Customer Num

21608 20/10/2013 148 21610 20/10/2013 356 21613 21/10/2013 408 21614 21/10/2013 282 21617 23/10/2013 608 21619 23/10/2013 148 21623 23/10/2013 608

Rep Rep Num

Last Name

First Name City Commission Rate

20 Kaiser Valerie Grove $20,542 0.05 35 Hull Richard Sheldon $39,216 0.07 65 Perez Juan Fillmore $23,487 0.05

Notes on the data: • Primary keys have

been underlined.

(Sample Questions)

Write SQL statements to retrieve the following information from the database. A) List the number and name of all customers that are represented by sales rep 35 and

that have credit limits of $10,000. [2marks] SELECT CustomerNum, CustomerName (0.5 mark) FROM Customer (0.5 mark) WHERE RepNum='35' And CreditLimit=10000; (1 mark)

B) List the number and name of every customer represented by Juan Perez. [2.5marks]

SELECT CustomerNum, CustomerName (0.5 mark) FROM Customer, Rep (0.5 mark) WHERE Customer.RepNum=Rep.RepNum (0.5 mark) AND LastName='Perez' (0.5 mark) AND FirstName='Juan'; (0.5 mark)

C) List the part class and the sum of the number of units on hand for each part class only

if the sum (of units on hand for the part) is bigger than 70. The result of this query should look like the table as below. [3marks]

Class Sum on Hand HW 93 SG 54

SELECT Class, SUM(OnHand) as [Sum on Hand] (1 mark) FROM Part (0.5 mark) GROUP BY Class (0.5 mark) HAVING SUM(OnHand)>70; (1 mark)

D) List OrderDate, OrderNum, PartNum, and (Part) Description for every OrderLine.

Sort the results into OrderNum and then PartNum. [3marks]

SELECT OrderNum, PartNum, OrderDate (0.5 mark) FROM OrderLine, Orders, Part (1 mark) WHERE OrderLine.OrderNum=Orders.OrderNum (0.5 mark) AND OrderLine.PartNum=Part.PartNum (0.5 mark) ORDER BY OrderNum, PartNum; (0.5 mark) E) List the number and description of every part that is ordered by a customer living in

Grove but not managed by a rep who is from the same city (Grove). [4.5marks]

(Sample Questions)

SELECT PartNum, Description (0.5 mark) FROM Part, OrderLine, Orders, Customer (0.5 mark) WHERE Part.PartNum = OrderLine.OrderNum (0.5 mark) AND OrderLine.PartNum = Orders.OrderNum (0.5 mark) AND Orders.CustomerNum = Customer.CustomerNum (0.5 mark) AND Customer.City = ‘Grove’ (0.5 mark) AND Customer.RepNum NOT IN (SELECT RepNum (1.5 mark) FROM Rep WHERE Rep.City <> Grove) 3. Using the following description, draw an ER diagram to model the database for

Telstra to manage and service for all public phones installed in areas over all Australia.

All tables in the database should at least in 3NF and without multi-values attributes. You need to use Crow’s foot notation. Your diagram should show all relevant attributes including primary and foreign keys. Indicate all connectivities, cardinalities and optionalities if any. State any assumptions you establish. Telstra divides Australia into a number of areas for the servicing of its public phones. Each area has an area-no, area-name, head-office-phone and up to 500 public phones to service. Some new areas may not have any public phone installed yet. Each public phone has a unique number, a location, a date-installed and a payment type (ie. D = cash only, S = smart card and cash, C = credit card and cash.). Telstra requires details on all calls made by each public phone including the date, time, destination phone number, duration, price code (ie. L = local, N = night rate, P = peak rate and E = economy) as well as the payment method (ie. D if cash, S if smart card and C if credit card). Each call must be calculated by only one price code. There are various price-codes available and each price-code is presented by the connection charge and the “per minute” charge. For example, local calls are a flat rate of 40c but other pricing codes have a connect charge plus a “per minute”. Connect charges are N-10c, E-20c and P-30c. The per-minute charges are N = 12c per min, E = 20c per min and P = 40c per min. About each month each public phone gets maintenance. Telstra need to record the maintenance date, the id of the repairer, the cash collected and any repair description.

(Sample Questions)

Solution draft

Area

PK Area_No

Area_Name HeadOffice_Phone

Phone

PK Phone_ID

Location Date_Installed Payment_Type FK1 Area_No

Maintenance

PK Date PK,FK1 Phone_ID

Repairer_ID Cash_Collected Repair_Desc MaintenanceCol1

Call

PK Call_Date PK Call_Time PK,FK1 Phone_ID

Destination_PhNo Duration Payment_Method FK2 Price_Code

PriceCode

PK Price_Code

Per_Min Connect

has gets

made

is applied for

(0, 500)

Assumptions:

- Some (new) phones may not have any maintenance record yet. - Some (new) phones may not have any call made yet. - There can be a price code which hasn’t been applied by any call.

Section A: Multiple Choice XX marks
Section B: Miscellaneous Questions XX marks