4. List which worker (by name) has worked for which project (by name). Sort the list in the ascending order of the age of the worker. [25]

8. Create the DDL for the creation of a Customer Table with 8 columns including data types, an identity column that increments, along with a primary key, be sure to allow the data structure to support a type 2 slowly changing dimension.

Module 4 Assessment Part 1 Purpose You will practice using SQL in the Access environment. You will examine data from a sample database using simple, single table select queries.

Assume that the following requirements were collected for this application: 1. The University Library has approximately 60,000 members, 200,000 titles, and 500,000 volumes (an average of 2 coples per book). About 10% of the volumes are out on loan at any one time. 2. The librarians ensure that the books that members want to borrow are available when the members want to borrow them. Also, the librarians must know how many coples of each book are in the library or out on loan at any given time. 3. A catalog of books is available online that lists books by author, title, and subject area. For each title in the library, a book description is kept in the catalog; the description ranges from one sentence to several pages. The reference librarians want to be able to access this description when members request Information about a book. 4. Library staff includes chlef librarian, departmental associate librarians, reference librarians, check-out staff, and library assistants. 5. Books can be checked out for 21 days. Members are allowed to have only five books out at a time. Members usually return books within three to four weeks. 6. Most members know that they have one week of grace before a notice is sent to them, so they try to return books before the grace period ends. About 5% of the members have to be sent reminders to return books. Most overdue books are returned within a month of the due date. Approximately 5% of the overdue books are either kept or never returned. 7. The most active members of the library are defined as those who borrow books at least ten times during the year. The top 1% of membership does 15% of the borrowing, and the top 10% of the membership does 40% of the borrowing. About 20% of the members are totally inactive in that they are members who never borrow. 8. To become a member of the library, applicants fill out a form Including their SSN, campus and home mailing addresses, and phone numbers. The librarians issue a numbered, machine-readable card with the member's photo on it. This card is good for four years. A month before a card expires, a notice is sent to a member for renewal. 9. Professors at the Institute are considered automatic members. When a new faculty member joins the Institute, his or her Information is pulled from the employee records and a library card is malled to his or her campus address. Professors are allowed to check out books for three- month intervals and have a two-week grace period. Renewal notices to professors are sent to their campus address. 10.The library does not lend some books, such as reference books, rare books, and maps. The librarians must differentiate between books that can be lent and those that cannot be lent. In addition, the librarians have a list of some books they are interested in acquiring but cannot obtain, such as rare or out-of-print books and books that were lost or destroyed but have not been replaced. The librarians must have a system that keeps track of books that cannot be lent as well as books that they are interested in acquiring. 11.Some books may have the same title; therefore, the title cannot be used as a means of Identification. Every book is identified by its International Standard Book Number (ISBN), a unique International code assigned to all books. Two books with the same title can have different ISBNS if they are in different languages or have different bindings (hardcover or softcover). Editions of the same book have different ISBNS. In first part of the assignment, you will first design an EER schema diagram based upon the LIBRARY database requirements specified above and create an EER schema diagram and documentation report describing your design choices. As part of this assignment, you should identify any missing or Incomplete requirements, and explicitly state them in your documentation. You should also explicitly state any assumptions you made that were not part of the requirements listed above. The second part of the assignment will be to map the EER schema design to a relational database schema, and create the tables corresponding to the relational schema using the ORACLE DBMS (or MySQL). You will add to your report a listing of the CREATE TABLE statements. Specify as many constraints (key, referential integrity) as you can in the relational schema. You should state the choices you made during the EER-to-relational mapping, and the reasons for your choices. (40%) The third part of the project is to load some data into the database, and apply certain update transactions and retrieval queries. You will create your own data. Include at least 10 members, 5 staffs, and at least 30 titles of different types. (20%) The following are the tasks for the third part of the project: 1. Load some initial data (as discussed above) into the database tables that you created in Part 2 of the assignment. You can either write a loading program, or use SQL/PLUS (insert command), or use SQL/FORMS. Your data should be kept in files so that it can easily be reloaded during debugging. The data format should be designed by you. (Note: You can also use the transactions created by you in item 3 below to load some of the data). 2. Write queries to retrieve and print all the data you entered. Try to print the data so that it is easy to understand (for example, print appropriate headings, such as: Authors, Book Title, Subject area etc.). 3. Write a query that will prepare a report for weekly Borrowing activity by Subject area, by Author, number of copies and number of days loaned out. 4. Write the following database update transactions using either PRO*C or JAVA/JDBC or PHP or some other programming language or scripting language. 4.1 The first transaction is to add information about a new MEMBER. 4.2 The second transaction is to add all the information about a new BOOK. 4.3 The third transaction is to add all the information about a new BORROW (LOAN) (this must find the book from the catalog). 4.4 The fourth transaction is to handle the return of a book. This transaction should print a return receipt with the details of the book and days when it was borrowed and returned etc. 4.5 The fifth transaction is to renew the membership. 5. Write following two triggers - 5.1 To notify a member about the outstanding overdue book. 5.2 To notify a member about his membership renewal. 6. Each transaction should have a user friendly interface to enter the information needed by the transaction. This can either be a Web-based interface, a command line interface, or a forms interface.

Task 1 Background A departmental store issues in-store-credit-card to facilitate and encourage customers to make more purchases in the store. The in-store-credit-card is available to public members who wish to apply. Card holder is given a credit limit (based on card holder's income) for making purchases in the store. At the end of the month, card holder will be billed for the amount spent. Premium card holder can earn bonus points when making purchases, and the bonus points can be used to redeem merchandise. B) Based on the relational schemas, provide SQL statements to create the tables. You must choose an appropriate data type and length for each column. You must also include appropriate check constraints and foreign key constraints to ensure data integrity C) To prepare for possible changes in the near future, the store owner requests for a set of ALTER Table statements to alter the table structure without dropping and re-creating the tables. The changes are stated below.

1. Basic association rule creation manually. The 'database' below has four transactions. What association rules can be found in this set, if the minimum support (i.e. coverage) is 60% and the minimum confidence (i.e. accuracy) is 80%? Trans_id Itemlist T1 {K, A, D, B} T2 {D, A C, E, B} T3 {C, A, B, E) T4 {B, A, D} Show each step of your calculation in details (i.e. item sets containing one item, two items like this), as each step will be graded individually.

3. Given the following set of functional dependencies, use Armstrong's axioms to derive (i), (ii), and (iii). 1. X→ Y 2. Z→Y 3. V → XYZ 4. XZ → V (1) (ii) XZ → YV (iii) XZ → VXYZ V → VXYZ Write each derivation (proof), justifying each step, the same way we do it in the presentation (e.g. see slide 9). Don't forget to write which axiom is used for each step.

1. Design a database to keep track of information for an art museum. Assume that the following requirements were collected: The museum has a collection of ART_OBJECTS. Each ART_OBJECT has a unique Id_no, an Artist (if known), a Year (when it was created, if known), a Title, and a Description. The art objects are categorized in several ways, as discussed below. . ART_OBJECTS are categorized based on their type. There are three main types: PAINTING, SCULPTURE, and STATUE, plus another type called OTHER to accommodate objects that do not fall into one of the three main types. • A PAINTING has a Paint_type (oil, watercolor, etc.), material on which it is Drawn_on (paper, canvas, wood, etc.), and Style (modern, abstract, etc.). A SCULPTURE or a statue has a Material from which it was created (wood, stone, etc.), Height, Weight, and Style. ART_OBJECTS are categorized as either PERMANENT COLLECTION (objects that are owned by the museum) and BORROWED. Information captured about objects in the PERMANENT_COLLECTION includes Date_acquired, Status (on display, on loan, or stored), and Cost. • Information captured about BORROWED objects includes the Collection from which it was borrowed, Date_borrowed, and Date_returned. • Information describing the country or culture of Origin (Italian, Egyptian, American, Indian, and so forth) and Epoch (Renaissance, Modern, Ancient, and so forth) is captured for each ART_OBJECT. The museum keeps track of ARTIST information, if known: Name, DateBorn (if known), Date_died (if not living), Country_of_origin, Epoch, Main_style, and Description. The Name is assumed to be unique. Different EXHIBITIONS occur, each having a Name, Start_date, and End_date. EXHIBITIONS are related to all the art objects that were on display during the exhibition. • Information is kept on other COLLECTIONS with which the museum interacts, including Name (unique), Type (museum, personal, etc.), Description, Address, Phone, and current Contact person. Draw an EER schema diagram for this application. Discuss any assumptions you make, and that justify your EER design choices. Map the following EER diagram from the above step into a relational database

4. Given a particular instance of a relation, we can actually figure out all functional dependencies that are satisfied by the relation¹. For example, given a relation below, we can tell that X→Y, because in every two tuples for which the values of X are equal, values of Y are also the same. Using this idea, list all non-trivial functional dependencies satisfied by the following relation:

5. Consider the following relation schema S(A, B, C, D, E) and a set of functional dependencies: AB-C CD-E 1 Keep in mind, these may not be "true" FDs though, because we will determine them exclusively based on the current data (i.e., a particular instance of relation) and changing that data can change FDs as well. DE-B a) Show that ADE is a candidate key (proof with step-by-step justification). b) Other candidate keys for this set of FDs are ABD and ACD. Given that, show why this relation is not in BCNF. Explain. c) Give a lossless decomposition into BCNF. Do it step by step, illustrating each split, similar to how we do it in our presentation. List candidate keys for all resulting tables. List all remaining FDs.