Download Database Design and Implementation - Project Report | CSC 472 and more Study Guides, Projects, Research Deductive Database Systems in PDF only on Docsity!
CSC 472: Introduction to Database Systems Project: Database Design and Implementation Instructor: Ratko Orlandic
The purpose of this project is to practice the design and implementation of a realistic database of your choice. Through this exercise, you will explore practical aspects of database design and implementation. The emphasis of the project is on the relational database design into 3NF. Following the design, you will implement the database system using SQL*Plus in Oracle 10g.
Part I: Logical Database Design
You should choose some facet of life that you feel needs to be modeled by a database. Then, you will design a relational database schema that adequately represents relevant information, and show its usefulness by providing a set of queries which your database will be able to support. In the process, you must use relational modeling tools and techniques that we examined in the lectures. In concrete terms, this part of the project consists of the following steps:
[1] Begin by choosing an application , e.g. a database for a local volleyball league or a certain society. It must be a non-trivial system, but not large. As a general guideline, you should have three or four entity sets, each with 2-5 attributes, and two, three, or four relationship sets. Avoid banking and student-course databases; they tend to be boring. Demonstrate imagination in choosing the application.
[2] Formulate in English 5 realistic queries , which will often be posed to the database. In the real world, you would include here many realistic queries you believe would be useful to somebody using the database system. However, for the purposes of this project, 5 queries will suffice. To have 2 or 3 complex queries (those that involve multiple tables) is desirable, but realism is more important. 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 spouses of people in a society if no user of your database will be asking for that. On the other hand, additional information, not reflected in your queries, should be included in the database if you feel that somebody may be interested in it.
[3] Specify the assumptions about the database in English. Here you discuss your assumptions about entities and relationships, and possible constraints on data. For example, you state here that an investor may hold many stocks, or that an employee can work in only one department. These assumptions will later help you formulate keys, relationship cardinalities, and functional dependencies. Be as brief as possible, but give enough detail as to avoid possible misinterpretation of data. Always assume you are talking to intelligent people. So, do not discuss something that is obvious. However, even these implicit assumptions must later be reflected in your design. Don't make many simplifying assumptions. Your database should be able to account for real-world situations as you perceive them.
[4] Produce an ER diagram for your database, which will reflect your choice of entity sets, their relevant attributes (2-5), and the relationships among them. Make your relationships realistic. Recall that relationship sets may also have their own attributes. On the ER diagram,
specify primary keys and relationship cardinalities in accord to the assumptions of Step 3. [Lectures 9 and 10]
[5] Convert the ER diagram into the relational database schema using the rules that we examined, underlining primary keys. Unless a relation is redundant, each entity set and each relationship set should be represented by a relation schema in which you list its name and attributes. Do not forget to eliminate redundant relations. [Lecture 10; also useful: lecture 5]
[6] For each relation schema obtained in the previous step, specify a set of non-trivial functional dependencies (FDs) satisfied by the schema. These FDs should not violate the constraints stated in Step 3 and the ER diagram. Recall that a dependency X → Y is trivial if Y ⊂ X. Avoid FDs that can be derived from others (e.g., using Armstrong Axioms). Based on the identified functional dependencies, find all keys (candidate keys) of each relation. Recall, no proper subset of attributes in a key can form a key. To verify that an attribute or attributes is indeed a key, you may employ the attribute-closure technique examined in the class. Based on the analysis of keys and FDs, for each table, determine whether it is in 3NF. If a table is not in 3NF, explain why. Note, at least one table must be in less than 3NF, so that you can later decompose it. [Lectures 11 and 13; also useful: lectures 5, 8, and 12]
[7] P erform 3NF normalization of schemas that are not in 3NF. For this project, you should use 3NF as your targeted normal form. Recall, if BCNF is the targeted goal, one must verify that the decomposition(s) did not result in a loss of functional dependencies. This is not necessary with 3NF normalization. [Lecture 13]
[8] Specify the schemas of all resulting relations in your database. Give each relation a meaningful name. For each relation schema, specify its name as well as its attributes separated by commas and placed in brackets. Underline the primary key attribute(s) on each schema.
[9] Formulate the queries of Step 2 using either Relational Algebra or SQL. If you are unable to state the queries, this is probably because your database is not powerful enough. Then, go back to step 3 and see how the database can be improved, repeating the subsequent steps until you are satisfied with your design and all queries can be formulated. The choice between Relational Algebra and SQL is up to you. Choose the one that you feel more comfortable with. [Lecture 6 and 7 or Lectures 15 and 16; also useful: SQL in Oracle 10g (Part 3)]
[10] Often, database systems are unable to support some reasonable queries even in the area of discourse. I expect that your database system will be one of them. Thus, to evaluate the power of your database system, you must also identify some reasonable queries that your database will NOT be able to support. For this project, it is sufficient to state in English two such queries.
Part II: Implementation in Oracle 10g
After the logical database design, database designer/administrator must implement the database system using an actual DBMS. For this part, you should keep handy SQL Assignments 1-3 and the corresponding readings.
[1] {30 points} Your task is to implement your database system in SQL on Oracle 10g using your SQL skills acquired through the programming assignments. In particular, create in Notepad script file called “ProjectInput.sql” with the commands indicated below in the given order. Then, execute the script file in SQL*Plus redirecting the output to the file “ProjectOutput.lst”. a) Drop each table (cascading constraints) you plan to create. This is a good programming practice when you create new database. b) Create tables obtained in Step 8 of Part I of this project. Each table must have a primary key that was identified in the design stage. c) Use the DESCRIBE command for each table in the database to verify that the columns have been correctly defined. d) Insert 4-8 records into each table. (NOTE: if you have foreign key constraints, you must order the insertions in a way that these constraints are not violated.) e) Make sure that the insertions are committed to the database. f) Using SELECT *, print the contents of each table. g) Using appropriate SQL commands, perform the queries formulated in Step 9 of Part I.
Note that the maximum score for the second (implementation) part of the project is 30%.
Submission On or before the due date, submit through digital drop box in a single zipped file called “_Project.zip”: 1) the Word file “Design.doc” with the design report; 2) the script file “ProjectInput.sql”; and 3) the corresponding output file “ProjectOutput.lst”. Submission by the due date of all requested documents, even if incorrect, is 20%. Thus, the maximum score for the project is 105%. The score will be multiplied by the weight assigned to this project (see the syllabus).
Final Remarks While these project guidelines are fairly representative of the process of logical database design, we have adopted here some simplifying assumptions. Toward the end of the course, I will post a one-page checklist of logical database design you should use in practice whenever you are in a position to design a new database system or evaluate existing ones.
Please start early. This is not meant to be a trivial project, but one that gives you a taste of challenges you will face as you go up the ladder of database professions. Good luck!
