?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

Order a unique copy of this paper
(550 words)

Approximate price: $22

Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency

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