• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Advanced Database Management Systems ( Map the ER diagram into Oracle OR-DBMS model)

 
Emmanuel Eichie
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kindly assist with this home work.I ama masters student and i am having issues solving the homework below.Kindly assist.




CPE540 – Advanced Database Management Systems
Spring 2012
Homework Assignment 1: Part 2 & 3
Due date: 14’th of April @ 11:59 PM

Part 2: Map the ER diagram into Oracle OR-DBMS model-25 points

Convert the sample EER conceptual schema (db1_part1_solution.jpg) posted on Moodle into tables and then implement these tables in the Oracle database on the server provided as mentioned in the class (Will provide the link here).(Note: some updates might be done in the provided diagram compared the first part of the homework to ease the table creation)

Note: You are required to populate sufficient data to test your database schemas and the queries in Part3. Also, go through the queries in part3 before creating the tables to make reasonable assumptions regarding the attributes unavailable in the files provided and fill them out.


Reduction Guidelines for Oracle OR-DBMS
- Must use Oracle Object-Relational features
- Must use user-defined subtypes for specializations
- Must use user-defined object types for composite attributes.
- Use reference for foreign key.
- Do not use triggers.

Use reference for foreign keys and specify what action should be taken in case of update and/or deletion of the referenced tuple/row (i.e., cascade, reject, or set default/null).

Reference: Refer to Oracle manual for information on how to create tables, indexes,
insert data, etc. (lbdwww.epfl.ch/e/teaching/BDA/support/OR%20Oracle.pdf).


Part 3: Queries on the database
Write the following queries in Oracle SQL and run them on your database developed as mentioned in Part 2 of this assignment.

Q1. Find the maximum, minimum and average price of all motels, resorts and bed & breakfast hotels?

Q2. Find all vehicle license numbers rented by all customers who reserved a given motel (Choose a motel name as per your data).
Q.3 Find number of customers per car rental branches in a given State (Choose a state name as per your data).

Q.4 Find the names of all agents who have done reservations on all types of
hotels.

Q.5 Find all customers who have reserved cars from the same agent more than
twice.

Q.6 Given a customer (SSN) find number of cities that the customer has traveled in a given time period (Choose the time period as per your data).
Hint: cities traveled can be obtained from reserved hotels.

Q.7 Find all agents that have made more than 10 car rentals and more than 5 hotel reservations in a given month.



Submission Guidelines

1. Your submission of part2 and part3 should include one createdb.sql file,
one dropdb.sql file, seven .sql files for queries described in part 3 (named q1.sql to q7.sql), and one readme.txt file, which include your assumptions and comments. Submission most probably will be through Moodle account. (Will notify you in the class about that)

2. createdb.sql file should create required types, tables, indexes if required, generate primary keys, ... , and populate sufficient data based on the skeleton data provided. “Sufficient data” means enough data such that your queries return something, but not everything. There is 60 points penalty if this file is missing since it is not possible to check your queries without any data.

3. The dropdb.sql file should drop all types and tables that are created by createdb.sql. There is 10 points penalty if this file is missing from your submission or if it does not drop all of your database objects.

4. q1.sql ~ q7.sql query files should contain SQL statements for queries Q1 to Q7 described in part 3 respectively. If you need to write two or more SQLs for ONE step, then they should be written after each other in ONE file. (e.g., do not create q1-1.sql and q1-2.sql if you need to have 2 SQLs for Q1, rather create only one q1.sql and have both SQLs in that file).

5. The readme.txt file must have your name, Student ID, your name, your oracle user name, oracle password, the name of the database and tables that your createdb.sql file generates. There is 25 points penalty if this file or some of the required information is missing from your submission.

6. You must make a .tar file to include all of your files in one file
(<your_username>_hw1.tar) using the following command: >

<your_username>_hw1.tar createdb.sql dropdb.sql readme.txt q1.sql q2.sql q3.sql q4.sql q5.sql q6.sql q7.sql

For example, if your username is John, then your tar file should be John_hw1.tar.

7. You need to submit the 2nd and 3rd part of your assignment electronically through moodle system

8. We run your sql files in the following order:

$ sqlplus your_oracle_username/your_password @createdb.sql
$ sqlplus your_oracle_username/your_password @q1.sql
$ sqlplus your_oracle_username/your_password @q2.sql
$ sqlplus your_oracle_username/your_password @q3.sql
$ sqlplus your_oracle_username/your_password @q4.sql
$ sqlplus your_oracle_username/your_password @q5.sql
$ sqlplus your_oracle_username/your_password @q6.sql
$ sqlplus your_oracle_username/your_password @q7.sql

Note that you should run your own “dropdb.sql” file or delete your database in sqlplus right before submitting your assignment otherwise Oracle will return an error when we try to create your database and publish your data again by running your “createdb.sql” file. This is because your database already exists. You need to make sure that Oracle does not return ANY error while running these files. If Oracle returns ANY error during execution of any of the above files, we make no attempt to fix the problem, and you will lose the points associated with those steps.


9. Start working on your assignment early.



GOOD LUCK
 
Anayonkar Shivalkar
Bartender
Posts: 1557
5
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Emmanuel Eichie,

Welcome to CodeRanch!

Please DoYourOwnHomework. Also, please CarefullyChooseOneForum. I don't know what this assignment has anything to do with 'Beginning Java'.
 
Campbell Ritchie
Sheriff
Posts: 51453
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anayonkar Shivalkar wrote:. . . I don't know what this assignment has anything to do with 'Beginning Java'.
Nor do I so off to the databases forum with it!

And welcome, again
 
Emmanuel Eichie
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey!
Don't get me wrong,I didn't ask for my homework to be done for me i just asked if i can get any form of assistance, so i see no reason why a fuss should be made out of it.I registered on javaRanch with an intention to get some knowledge on java but instead i got something else.
Thanks allthesame.
'll find help elsewhere.
 
Rob Spoor
Sheriff
Pie
Posts: 20753
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You may have had good intentions, but your initial post did look like you wanted others to do the work for you. You only say you have issues, then post the entire assignment. Nowhere do you specify what issues you have.
So let's start over. What issues do you have? What have you done already? In other words, TellTheDetails - not about the assignment (we already have that ), but about your progress on solving the issue.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic