A consulting company has a lunch room, 12 conference rooms, 6 LCD projectors, 3 portable PCs, etc. They need to be able to schedule each of these
A consulting company has a lunch room, 12 conference rooms, 6 LCD projectors, 3 portable PCs, etc. They need to be able to schedule each of these resources for a given day and time period and avoid conflicting use of resources. Also, management would like reporting on resource utilization per week, month, year. They are also considering renting out resources to other companies if resource utilization is low. Queries might include:
When is the next day resource X is free between 1:00 and 5:00.
How many hours per week on average is conference room X occupied.
Intro to Group Project
Prof. Kline
Group Project
The course requires that students complete a group project. Students will go through the entire database development process from E-R model to normalization, creating tables, queries, views, forms and reports.
Up to four students on a team.
Project development activities are facilitated by research, class lectures and labs.
It is recommended that students review the hair salon database project. See project folder. It can serve as a template for your work.
Project Ideas
5-*
You are encouraged to come up with projects of your own – from work (so long as data is not proprietary), student groups/clubs, social organizations. The project ideas listed below can also serve as a guide or as inspiration for a project topic….All the following ideas will need to be expanded….
Scheduling Application – A consulting company has a lunch room, 12 conference rooms, 6 LCD projectors, 3 portable PCs, etc. They need to be able to schedule each of these resources for a given day and time period and avoid conflicting use of resources. Also, management would like reporting on resource utilization per week, month, year. They are also considering renting out resources to other companies if resource utilization is low. Queries might include:
When is the next day resource X is free between 1:00 and 5:00.
How many hours per week on average is conference room X occupied.
Supply Cabinet – A company centrally maintains supplies for each of its branch offices. They need a database to keep track of what they have in stock, requests from branch offices for supplies and purchasing of supplies from vendors. Should keep track of the vendor with the best price for a particular supply. They would also like to minimize shipping costs by shipping several supplies at one time to a given branch office. Queries might include:
When should supply X be replenished
How many shipments, on average, go out to the branch offices per week ?
Baseball Statistics – A professional baseball team would like to maintain a database that records player statistics on all team members and complete records of every game (on an inning-by-inning basis). Each player would have a set of offense and defense statistics. Queries include:
What is the batting average for player X
Who is the best relief pitcher to use against a left-handed batter
Project Milestones
- Project topic is identified and defined
- Systems analysis and E-R model
- Relational model and normalization
- Midterm project deliverables due
- DDL and DML programming of design
- Database implementation using SQL completed. Query database
- Final project deliverables due
5-*
1. Develop a proposal for the project
5-*
First students should identify the database system application they want to design.
Informal discussions with the instructor can help define the project.
Students should not continue working on the project unless reviewed and approved by the instructor.
Project proposals will include the following:
- A short narrative description of the problem or opportunity being addressed.
- High level identification of information needs – what information would help solve the problem or allow a system user to take advantage of the opportunity.
- To be reviewed by instructor.
2. E-R Model
5-*
This step derives an Entity Relationship (E-R) diagram using UML notation from the project proposal / user requirements (Step #1).
Students should use a E-R modeling tool such as MS Visio, Gliffy, LucidChart, etc. to complete this work. See the slide at end of this deck for possible tools.
For this step, all that is needed is essentially a UML class diagram.
An E-R model includes basic structures (entity, identifier, attributes, and relationships), and enhanced features (inheritance, composition, aggregation, intersect tables).
3. Relational model
5-*
This step converts the E-R entity diagram into a relational or logical model. During this conversion, foreign keys are propagated to the relations. Components of a relational model include: relations, tuples, attributes, primary key, foreign key.
The concept of cardinality / multiplicity is introduced. Relationships can now be 1:1, 1:many, Many : Many
Example of a relational (logical) model showing propagation of FKs:
CustomerID (FK)
(PK)
3. Relational model, cont.
5-*
For a majority of conversions of ER Models, the following general steps apply:
Entities – In general, each Entity will be converted directly to a Relation.
The Attributes of the entity become the Attributes of the Relation.
The Identifier of the Entity becomes a Primary Key of the Relation.
Relationships will be mapped as “Foreign Keys”.
There are a number of variations to this step. Use UML to represent multiplicity.
4. Normalization to Physical Model
Students will next convert the relational model through a process of normalization to derive a normalized model. This step will require students to normalize relations to 3NF. Adding data to the relation will assist in this process.
- Output of this process will be a physical model represented by a normalized set of schema that is in 3NF.
- Please identify and show functional dependencies for relations in 3NF.
5. Midterm project deliverable
5-*
Having completed steps 1 – 4, students will need to submit two midterm project deliverables. Submit your work in the form of a Word and powerpoint document. Use the link provided to upload to Blackboard.
For the midterm project deliverable, please submit the following:
- The project proposal. Team members. Short description of the application & business issue it addresses. What value does it deliver to the business. Format: Word document / yourGroup_MidProj.doc.
- Relational model normalized to 3NF.
- Functional dependencies for each relation are identified and shown (FD1..FDn).
- PK and FK propagation
5) Copy and paste to a powerpoint document all work done for step 2. Format: PPT / yourGroup_MidProjRelModel.ppt.
6. Database Implementation using Microsoft Access
5-*
Student should then implement the database schema created in Step #4.
For each relation, write a SQL DDL CREATE TABLE statement. The DDL statement should include the creation of the relation, all its attributes, primary and foreign keys, other constraints.
Data should be added to each table by writing SQL INSERT statements. Students should provide enough data to enable subsequent queries involving single and multiple tables (5 – 8 rows).
Please save your SQL DDL statements.
7. Application implementation using Access
5-*
In this step queries and views are created to access the database created in step #6.
DML Queries – 10 – 15 should range from simple to complex and answer business questions about the project topic. Queries should include join and subquery.
Views – representing different views of data either for management, operations or end users.
Forms – for example, if the business takes orders from customers, develop a form with Orders and Order items with lookups for customers and products or services.
Example or description of VBA code for business rules or use with Forms.
8. Final Project Deliverable
- Having completed steps 6 – 7, please submit work as a final project deliverable. Submit your work in the form of an Access file (yourGroup_FinalProj.accdb) using the upload link provided.
- All DDL definitions for your database complete with PKs, FKs, data referential integrity.
- 10 – 15 DML statements run with resultant reports
- In addition to submitting output from Steps 6 & 7, a short narrative that describes:
- The students experience (e.g. which step is easiest, hardest?)
- What you learned that you did not expect to
- What you would have done differently
- yourGroup_FinalProj.doc
- The instructor will select a few examples of work for class discussion during the last weeks of the course.
5-*
Project Milestones
Midterm deliverable due – week 8 (Steps 1 – 5)
Final project report due – week 14 (Steps 6 – 7)
Short oral presentation in class – week 14 / 15, if time
No late work permitted
Sample UML E-R Modeling Tools
- Visual Paradigm
http://www.visual-paradigm.com/solution/freeumltool/LucidChart
- Gliffy https://www.gliffy.com/
- lucidChart https://www.lucidchart.com/
- MS Visio
*
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
