This is CIS project.
Shelby4
TeamProject3.pptIntro to Team Project
5-*
Team Project
The course requires that students complete a team project. Teams will go through the entire database development process from E-R model to normalization, creating tables, queries, views, forms and reports.
Project development activities are facilitated by research, team discussion, class lectures and labs.
Teams consist of at most 4 members.
It is recommended that teams 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
- Groups are formed and project proposal reviewed / discussed
- Systems analysis and E-R model completed
- Logical modeling and Normalization
- Midterm project report due
- Physical Database implementation using SQL completed
- Final project report due
5-*
1. Develop a proposal for the project
5-*
First, groups should identify the database system application they want to design.
Informal discussions with the instructor can help define the project.
Groups should not continue working on the project unless reviewed and approved by the instructor.
Project proposals will include include:
- 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.
- Distribution of duties for the project, with team member names and roles.
- 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).
http://holowczak.com/drawing-entity-relationship-diagrams-with-uml-notation-using-lucidchart/
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)
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
The team will next convert the relational model through a process of normalization to derive a physical model. This step will require the team 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, the team will need to submit a midterm project deliverable. Submit your work in the form of a powerpoint document. Copy and paste to the powerpoint document your work done in the modeling tool of your choice.
The name of the project, and names of the team members are to be listed on the title page with the tasks that each member completed.
Submission is by one team member only.
For the midterm project deliverable, please submit the following:
- The team project proposal. Short description of the application & business issue it addresses.
- 1 relational model of your database.
- 1 physical model normalized to 3NF and represented by a set of database schema. Please show the functional dependencies for each relation in the model.
5-*
5-*
5-*
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
*
*
