Reference no: EM132156952

Term Project

Parts I - IV on Nov. 14

This project is designed to give you an opportunity to apply database theory as well as design methodology and techniques to a real world situation. You are required to create a database for an organization (e.g. a library, a movie rental company, a law firm, a driving school, or a company that specializes in IT training). In a group of 2 or 3 students, you will choose an organization, research how such an organization typically operates, and identify the part of the organization for which you are going to create a database application. You will then design and create this database application.

While keeping the size of the database application manageable for this assignment, try not to simplify it too much. For example, it is obviously not practical to create an integrated library system for this assignment; but it would be too simple if you just create a circulation system that simply keeps track of books, customers and checkout records. About 6 entities and 10 transactions should be a good size for this assignment. If the size of your project is too small (4 or fewer entities), a deduction will be applied to your final grade for this project in order to be fair to other students who carry out more complex projects.

Part 0 - Topic for term project and group assignments

You are required to send your instructor a short, one paragraph description of your term project. Describe the organization for which you will be creating a database and include the names of all the members of your group.

Part I - Requirements analysis

Working in groups of two or three students, research how your chosen part of the organization typically operates, i.e. what it does, how it functions, what information is involved, and how the information flows through it. Create a requirement specification, which includes the following:

1. A 1-page (single-space) description of your chosen part of the organization in terms of what it does and how it runs. Attach any forms necessary for illustration. You can use the overview of the DreamHome case study in the text as a model.

2. A requirements specification. This includes :

a. data requirements broken down by major objects / things (e.g. book, user, etc. in the case of an information system for a mini-­- library), and,

b. requirements for typical transactions (e.g. "list all users who have overdue books"). This will serve as the basis for the next step - database design. So, be as clear as possible. You can use Appendix B.1 in the text as a model. In addition, have a 1-­page discussion (single-­space) on what fact-­finding techniques you would use in order to discover users' information needs, how and why you would use them. For example, who would you interview and what questions would you ask if you use an Interviewing technique, or what documents would you examine if you use an Documentation Examination technique? What would each of these techniques be good for, what would you expect to find out using each of them? Make sure that your discussion is specific to your case.

Part II - ER modeling

Create and submit an Entity-Relationship (ER) model of the data requirements for your application using the UML notation (see Chapter 12 in your textbook and this article onDatabaseModelinginUML). Include the entities (with attributes) and relationships identified, and the participation and cardinality constraints. Note: if necessary, use the additional concepts of the Enhanced Entity-Relationship (EER) model (See Chapter 13 in your textbook). Make sure that the ER model matches the requirements specification you have created.

Part III: Development of relational model

Derive a relational schema from your ER model that represents the entities, attributes, and relationships. Describe the process of developing your specific relational schema, including what is needed to be done at each step of the process and the relations created or modified at each step. At the end of the process, provide the complete database schema using the following notation, with the primary and foreign keys identified:

Staff (staffNo, fName, lName, address, SIN, sex, DOB, deptNo, supervisor) Primary Key staffNo

Foreign Key deptNo references Department (deptNo)

Foreign Key supervisor references Staff (staffNo)

Include the following in your submission:

• A concise description of the process of deriving your relations from the ER model
• A complete list of relations derived from the ERD

Part IV - Implementation

• Create a database for your organization. Make sure that the database matches the relational model and that the constraints are correctly set in the database.
• Create four of the tables for your database. Where appropriate, set field and table properties, including any required indexes.
• Establish relationships between the four tables. Ensure that referential integrity is established between related tables.
• Enter some test data (approximately 5 rows) into each table.
• Implement the transaction requirements. At least four of the 10 transactions must be triggers designed to ensure the integrity and quality of your data and your tables. Try to use each query to demonstrate the breadth of your knowledge of SQL.

Include the following in your submission:

• Your phpmyadmin database file. Please name your file as lis533_termProject_groupNumber.sql
• A list of queries and triggers you have implemented, each including query name, a short description of what it does, and the SQL statement. Each group is to submit a single zip file that contains a database file and a PDF file with all your documents for Parts I-IV.