Questions & Answers

Creating Your First ERD There are some tricks and must do's when creating an ERD. Click the following link as we walk through some of the pertinent steps before starting your lab. Creating Your First ERDB

1. Simple PL/SQL Function

2. Parametric Function

3. Regex Function

CSE5330-005 Fall 2022 Project 1 In this project, you will get started on how to use a relational DBMS. You can either use the ORACLE RDBMS, or the MySQL system. You will use the interactive SQLPLUS facility, and the SQL programming facility, by creating tables, populating them with data, and querying and updating the tables. You should do the following: 1. Create the following tables for the US COVID-19 database whose schema is specified in Page 3 of this document: STATE, COUNTY, CONFIRMED CASES, DEATHS, VACCINATIONS. Write your CREATE TABLE statements in a text file and execute the commands from the file through SQLPLUS. You should capture the execution in a spool file that will be turned in. Specify appropriate key and referential integrity constraints. The data types for each attribute are given after the schema diagram. 2. Write one or more database programs to load the records that will be provided to you into each of the tables that you created. The supplied data is synthetic and may not represent the ground reality. You might have to pivot some data to suit to the schema given. You can use any programming or scripting language you are familiar with (JAVA with JDBC, Pro*C, PERL, PHP, Python, etc.). 3. Write down the queries for the English queries that are listed later. Execute each query and display its results. Capture your commands in spool files for turning in. 4. Execute 3 more Insert commands that attempt to insert 3 more records, such that the records violate the integrity constraints. Make each of the 3 records violate a different type of integrity constraint. Capture your commands in spool files for turning in. 5. Execute a SQL command to Delete a record that violates a referential integrity constraint. Capture your command in a spool file for turning in. 6. Repeat 5 but Insert three new records that do not violate any integrity constraints. Capture your commands in spool files for turning in. You should turn in to the GTA one or more Spool files as part of the assignment, including creating the tables and the query results. You should also turn in the source code for the programming part of the assignment for loading the data. Document your output when needed by writing down an explanation for each step (by editing the spool file); for example, explain the integrity constraints violated in item 5.

1. Create the database by run the create_tableql file.


2. Get the capacity and name of the oldest project [15]. a. Hint: start by sorting the projects in the increasing order of their start dates, so that the oldest one is at the top. After making sure that you have the projects in the right order, modify the query to print only the first row (use limit clause).

3. Get the name and the age of the youngest worker who got A [40] a. Hints: since the age and grade information are in two different tables (which two?), you'd have to first join the two. Use where clause to join them as discussed in class. b. Then add a clause to the where clause to print only those rows that contain grade 'a'. c. Once you have the workers who got 'a', you can select the youngest following approach similar to question 2.

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]

5. Find the workers from the birthcity that includes 'city' [20]. a. Hint: use the expression "like"


First Choice: Design a Simple Database using Ms. Access 2013-365 (any DATABASE of your choice) The Database (your project) must include, using: Tables (1 to 2 tables) The tables are the backbone and the storage container of the data entered into the database. If the tables are not set up correctly, with the correct relationships, then the database may be slow, give you the wrong results or not react the way you expect. So, take a bit of time when setting up your tables. Design view Design view gives you a more detailed view of the structure of the form. You can see the Header, Detail, and Footer sections for the form. You cannot see the underlying data while you are making design changes; however, there are certain tasks you can perform more easily in Design view than in Layout view. Datasheet view In the Open dialog box, select the database that you want to open, and then click Open. On the Create tab, in the Tables group, click Table. A new table is inserted in the database and the table opens in Datasheet view.. The Lookup Wizard The Lookup Wizard establishes a relationship between tables. It creates a foreign key that refers back to the primary key of another.

Second Choice: Create PDF or Word file (1-2 maximum Pages) about: the difference between Database management system and Database

Question #1. Consider the three transactions T1, T2 and T3, and the schedules S1, S2, S3 and S4 given below. Which of the schedules is (conflict) serializable? The subscript for each database operation in a schedule denotes the transaction number for that operation. For each schedule, show all conflicts, draw the precedence graph, determine and write down if it is serializable or not, and the equivalent serial schedules if exist.

Question #2. Consider the three transactions T1, T2, and 13, and the schedules $5 and S6 given below. Show all conflicts and draw the serializability (precedence) graphs for $5 and 56, and state whether each schedule is serializable or not. If a schedule is serializable, write down the equivalent serial schedule(s).

Question #3. Write a PHP script for each of the following questions: (a) Suppose you are given an array color with some values. Write a PHP script to get the first element of the array.


PART 2: You must have the Microsoft Access database system installed for this part. Using your physical model, create a database in Microsoft Access. Each entity in your diagram represents a table object in your database. Start by creating a new, blank database.

BACKGROUND You are provided with a conceptual and logical data model based on the narrative in Assignment 1A. They are included in the appendix of this document.


Examine the forms available in Microsoft Access. Create a simple form in Microsoft Access. Examine data validation concepts. Use data validation in a form in Microsoft Access. Apply encryption to an Access database.

Functional Dependencies Show all the working. Do not simply write answers. If essential steps, such as edge diagrams, transitive inferences are missing, points will be deducted. A-1 Transitive Dependency and Keys (5 points) You have a relation R(L,M,N,O,P,Q) and a set of functional dependencies F = {LNO-M, MN-LOP, NO, OP-LN}. • [2pt] Can we infer NP → LM from F? [3pt] Can we infer NQ → LO from F?

A-2 Keys (10 points) (i) [5pt] Find all the candidate keys of the Relation R(ABCDE) with FD's: DC, CE →A, DA, and AE → D (ii) [5pt] Determine all the candidate and superkeys of the relation R(ABCDEF) with FD's: AEF → C, BF→ C, EF→ D, and ACDE - F

A-3 Minimal Cover (10 points) [10 pt] Find all minimal covers for the following set F of functional dependencies. XZ, XYZ, Z- UT, ZUT, ZW → XY, WT → Z Show your working clearly. Points will be deducted if you do not show the extraneous attributes, and their elimination.

A-4 Equivalence (15 points) [15pt] Consider the following set of F.Ds. Determine if FD1 is equivalent to FD2 or to FD3: FD1: {BC->D, ACD->B, CG->B, CG->D, AB->C, C->A,D->E,BE->C,D->G,CE->A,CE->G} FD2: {AB->C,C->A,BC->D,CD->B,D->E,D->G,BE->C,CG->D} FD3: [AB->C,C->A,D->G,BE->C,CG->D,CE->G,BC->D,CD->B,D->E} You must show closure of each LHS attributes on the left hand side of each FD_i where i = {1,2,3} via going through the other FD set. Then establish the equivalence.

1. Evaluate the ER Diagram below. Based on your analysis, document five business rules.

2. Analyze the dependencies that exist in this table design and normalize it to the 3rd normal form. Note: Please provide the relation dependency diagram with annotations, as well as the final 3NF relations.

5. Create an ERD for a chain of Candy Stores. They will want to track customer, product, employee, store location, sale amount, discount type (if any), discount amount (if any). Be sure to include cardinality via crow's feet as well as identified PK and FK for each table. No other sample fields are necessary.

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.

9. Write a SELECT statement based on the tables in the third normal form from your response to Question 2. Include at least 1 join and 1 filter clause.

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.

Module 4 Assessment Part 3 Purpose In this assessment, you will practice using a variety of query functionality to include using functions, calculations, and parameters. You will also create an action query to update data in a table to demonstrate other ways to manipulate data using queries in Access.

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.

Task 2 Background A major multi-national corporation has employed you to research and implement SQL statements to support their data management operations. The company consists of several departments located in the cities all over the world. The company's database also contains information about the present and past jobs of its employees and about the present managerial structure. A conceptual schema of the database is given 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.

2. 2.1. What is the result of executing the following XQuery? for $m in 1 to 10 let Sn = $m + 1 where $m > 4 order by $m descending return $m*$n 2.2. Using the XML Document below (library with books), define the following queries in XQuery: (a) Give the titles of all Books sorted by Price. (b) Give the sorted list of titles of all Books that have a price with a value that is higher than 70. (c) Give the list of titles of all Books published by Addison-Wesley. (d) How many books were written by Abiteboul? (e) Give for each author, the number of books he has written.

1. Defined below are three relations' schemas and their sets of functional dependencies. R₁(A, B, C, D) A → BCD C → AB R₂(E, F, G, H, I, J, K) E → FG EFG H R₁(L, M, N, O, P, R) L MN LN → OPR R → LM a) For each of the relations R₁, R₂, and R3, check which of the following are candidate keys (can be more than one for each relation, or none). R₁: A, B, C, D, AB, BC, ABC R₂: E, FG, EFG, EIJK R₁: L, N, R, LN, MR, NMR b) For each of the relations R₁, R₂, and R, determine if it is in 3NF. c) For each of the relations R₁, R2, and R3 determine if it is in BCNF.

2. Decompose relation R₂ from problem 1 into two tables R21 and R22 so that they are both in BCNF decomposition with a lossless and dependency-preserving

No Search results found!

Kindly submit your queries
we will make sure available to you as soon as possible.

Search Other Question