?Database Design Project

Database Design ProjectNOTE, this is a team project. Up to three students constitute a team.The purpose of this project is to give you some experience in database design. We will explore both theoretical and practical aspects of the database design steps. Once you master it well, it is a simple matter of translating the theoretical concepts you developed into the terms of a concrete DBMS to implement the database. In our case, you can implement the database system using Oracle or MySQL.Part I:Conceptual/Logical Database DesignYou must choose some facet of real life that you feel needs to be modeled by a database. Then, you must design a relational database schema that adequately represents this information, and demonstrate its efficacy by providing a set of useful queries that your database can support. In the process, you must use standard relational modeling tools and techniques we examined in class. In concrete terms, this part of the project consists of the following steps:(1) Begin by choosing an application, e.g. the database for a local volleyball league or the database for a Society of Roasted-Shrimp Fans. It must be non-trivial, but not huge. As a general guideline, you should probably have about seven to ten entity sets and similar number of relationship sets.(2) Formulate in English at least 10 realistic queries you believe would be useful to somebody using the database. The queries must be realistic and you should have more than few complex queries. Note, the queries that the database system must be able to answer ultimately determine what information needs to be maintained in the database. For example, you need not include the information about best friends of students if no user of your database will be asking for that. On the other hand, some additional information, not reflected in your 10 queries, may be included in the database if you feel that somebody may be interested in it.(3) Specify the assumptions about the database in English (informally). Here you talk about attributes, keys, the nature of relationships between entities, etc. Do not discuss something that is obvious (e.g., that a student can take several courses). In addition, don’t make too many simplifying assumptions.(4) Give an ER diagram, which will reflect your choice of the entity sets, their relevant attributes, and the relationships among them. Again, make your relationships realistic and meaningful.(5) Convert the ER diagram into a relational database schema.(6) (Up to 5 extra points) For each relation schema obtained in Step(5), specify the functional dependencies satisfied by the schema. Based on the knowledge of functional dependencies, you can analyze how good your initial DB design is, i.e. the potential for repetition of information and other anomalies we examined in class. For each relation schema, specify the normal forms it satisfies.(7) (Up to 5 extra points) If necessary, perform decompositions of the relations, such that the entire database is in 3NF or BCNF. Note that if your initial design is non-trivial, then at least one table should be in 1Nfor 2NF, so that you can decompose it.(8) Formulate the queries in Step(2) using relational algebra. If, at this point, you are unable to state the queries in relational algebra, this is probably because your database is not powerful enough. Then, you must go back to step 3 and see how the database can be improved.(9) Of course, no database is capable of supporting all queries. Very often (in fact, it is almost always the case), databases are unable to support some reasonable ad hoc queries. I expect that your database will be one of them. Thus, to evaluate the database design properly, you must identify reasonable, but unanswerable queries as well, and explain why your database cannot support them. For the purposes of this project, it will be sufficient to state two queries of this kind in English.Part II:Report on Logical Database DesignGood DBAs must be able to prepare a full design report.You should hand in the full, legible report (hopefully typeset) with a cover page that gives your name, the title of the project, and its brief description. The final report should include sufficient detail to describe all steps of your design:(1) Describe what you have chosen as your database.(2) List at least 10 reasonable queries in English.(3) Add one or more atomic transactions to your query set.(4) State assumptions about the database as briefly as possible.(5) Give the ER diagram of the database.(6) Give the relational database schema.(7) (Up to 5 extra points) For each relation schema, give the associated FDs and specify the normal formsthe schema satisfies.(8) (Up to 5 extra points) Perform the decomposition into 3NF or BCNF and give the final databaseschema along with the FDs.(9) Give the relational algebra expressions for the queries formulated in step 2.(10) Give at least two reasonable, but unanswerable queries, and explain why the database cannot supportthem.(11) In conclusion, describe what you have learned from the project, and include your observations aboutthe power of relational systems and languages.Part III: ImplementationYour task now is to implement your database using Oracle or MySQL.(1) Create tables obtained in Step(7) of Part II.(2) Insert 10 to 15 records into each table and print the contents of each (using SELECT * ).(3) Compose SQL queries formulated in Step(8) of Part II;(4) (Up to 5 extra points): Add one or more atomic transactions to your query set;(5) Execute the queries and print the results.
database_design_project.docx

Unformatted Attachment Preview

Don't use plagiarized sources. Get Your Custom Essay on
?Database Design Project
Just from $13/Page
Order Essay

Database Design Project
NOTE, this is a team project. Up to three students constitute a team.
The purpose of this project is to give you some experience in database design. We will explore both theoretical and
practical aspects of the database design steps. Once you master it well, it is a simple matter of translating the
theoretical concepts you developed into the terms of a concrete DBMS to implement the database. In our case, you
can implement the database system using Oracle or MySQL.
Part I:
Conceptual/Logical Database Design
You must choose some facet of real life that you feel needs to be modeled by a database. Then, you must design a
relational database schema that adequately represents this information, and demonstrate its efficacy by providing a
set of useful queries that your database can support. In the process, you must use standard relational modeling tools
and techniques we examined in class. In concrete terms, this part of the project consists of the following steps:
1. (1) Begin by choosing an application, e.g. the database for a local volleyball league or the database for a
2.
3.
4.
5.
6.
7.
8.
Society of Roasted-Shrimp Fans. It must be non-trivial, but not huge. As a general guideline, you should
probably have about seven to ten entity sets and similar number of relationship sets.
(2) Formulate in English at least 10 realistic queries you believe would be useful to somebody using the
database. The queries must be realistic and you should have more than few complex queries. Note, the
queries that the database system must be able to answer ultimately determine what information needs to be
maintained in the database. For example, you need not include the information about best friends of
students if no user of your database will be asking for that. On the other hand, some additional information,
not reflected in your 10 queries, may be included in the database if you feel that somebody may be
interested in it.
(3) Specify the assumptions about the database in English (informally). Here you talk about attributes,
keys, the nature of relationships between entities, etc. Do not discuss something that is obvious (e.g., that a
student can take several courses). In addition, don’t make too many simplifying assumptions.
(4) Give an ER diagram, which will reflect your choice of the entity sets, their relevant attributes, and the
relationships among them. Again, make your relationships realistic and meaningful.
(5) Convert the ER diagram into a relational database schema.
(6) (Up to 5 extra points) For each relation schema obtained in Step(5), specify the functional
dependencies satisfied by the schema. Based on the knowledge of functional dependencies, you can
analyze how good your initial DB design is, i.e. the potential for repetition of information and other
anomalies we examined in class. For each relation schema, specify the normal forms it satisfies.
(7) (Up to 5 extra points) If necessary, perform decompositions of the relations, such that the entire
database is in 3NF or BCNF. Note that if your initial design is non-trivial, then at least one table should be
in 1Nfor 2NF, so that you can decompose it.
(8) Formulate the queries in Step(2) using relational algebra. If, at this point, you are unable to state
the queries in relational algebra, this is probably because your database is not powerful enough. Then, you
must go back to step 3 and see how the database can be improved.
(9) Of course, no database is capable of supporting all queries. Very often (in fact, it is almost always the case),
databases are unable to support some reasonable ad hoc queries. I expect that your database will be one of them.
Thus, to evaluate the database design properly, you must identify reasonable, but unanswerable queries as well, and
explain why your database cannot support them. For the purposes of this project, it will be sufficient to state two
queries of this kind in English.
Part II:
Report on Logical Database Design
Good DBAs must be able to prepare a full design report.
You should hand in the full, legible report (hopefully typeset) with a cover page that gives your name, the title of the
project, and its brief description. The final report should include sufficient detail to describe all steps of your design:
1.
2.
3.
4.
5.
6.
7.
(1)
(2)
(3)
(4)
(5)
(6)
(7)
Describe what you have chosen as your database.
List at least 10 reasonable queries in English.
Add one or more atomic transactions to your query set.
State assumptions about the database as briefly as possible.
Give the ER diagram of the database.
Give the relational database schema.
(Up to 5 extra points) For each relation schema, give the associated FDs and specify the normal forms
the schema satisfies.
8. (8) (Up to 5 extra points) Perform the decomposition into 3NF or BCNF and give the final database
schema along with the FDs.
9. (9) Give the relational algebra expressions for the queries formulated in step 2.
10. (10) Give at least two reasonable, but unanswerable queries, and explain why the database cannot support
them.
11. (11) In conclusion, describe what you have learned from the project, and include your observations about
the power of relational systems and languages.
Part III: Implementation
Your task now is to implement your database using Oracle or MySQL.
1.
2.
3.
4.
5.
(1)
(2)
(3)
(4)
(5)
Create tables obtained in Step(7) of Part II.
Insert 10 to 15 records into each table and print the contents of each (using SELECT * ).
Compose SQL queries formulated in Step(8) of Part II;
(Up to 5 extra points): Add one or more atomic transactions to your query set;
Execute the queries and print the results.

Purchase answer to see full
attachment

GradeAcers
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.

Order your essay today and save 15% with the discount code DISCOUNT15