Reference no: EM133292302
Database Planning and Management
Project
Part I: ERD and creation of tables/relationships
Part II: Creation of query and SQL statements
The following deliverables are required at the end of the project. They are examined below in detail:
1. Full-blown ERD using Crow foot's notation. Clearly identify the PK, FK, optional relationship, etc.
2. MS Access Database (alternatively, you can created on SQL Server or Postgres) with tables created and relationships built, based on the ERD created. Apply lecture concepts and Access tutorials to database design, such as, applying indexes to minimize the occurrence of duplicate entries, input mask, lookup, normalization, etc.
3. A written report explaining the design rationale and illustrating the key features of the system. Include user screen and explain them in detail (i.e. explain required fields, index etc.). Examples of design rationale include: why include certain field (e.g., derived attributes) in a table, why use certain attributes as primary keys. The idea is to demonstrate your awareness of design alternatives and their pros and cons.
4. Elaborate on the role and responsibilities of each member of the group, and the amount of time spent by each team member in that activity. A sample entry may look like:
|
Week Oct 21
|
|
|
Activity
|
|
|
Member
|
|
|
Hours
|
|
|
|
ERD
|
|
Joe
|
|
4.00
|
|
|
|
|
|
Chris
|
|
3.00
|
|
|
|
Table creation
|
|
Chris
|
|
1.00
|
|
|
|
|
|
Joan
|
|
4.00
|
|
|
|
Report
|
|
John
|
|
2.00
|
5. Peer Evaluation Form: Each member of the group will have to complete a peer review form that rates the contribution of the other members in the group.
6. Lessons Learned: This section will summarize the lessons learned by the team over the course of developing the application. The subject may range from what they will do differently next time they are assigned a project. Also provide ideas for team management issues and what the team as whole has to do next time around to improve their performance. You can also provide examples of problems (technical, team management, etc.) that the team faced and how they were overcome.
7. Presentation: Each team will be required to make a 10 minutes presentation on the project (both parts) on Dec 7 (Weds). The presentation may cover how the design/query/system meets the needs and requirements of the client and a demonstration of the application's functionality.
CASE
Your team is asked by a client (Kansas Heartland Lending Corporation, KHLC) to design a database so that KHLC managers can better manage its functions by keeping track of their customers, loan officers, underwriters, loans, and statements. Information about the KHLC is provided in a separate document.
As explained in the Deliverables Requirement on page 1, your team is asked to create a database (Deliverables #2) that will fullfill the operations described in this problem. The minimum required entities are Customer, Loan, Statement, LoanOfficer, and Underwriter. (There might be additional required entities that are not listed.). Create all of the required tables and all of the required relationships. Populate the tables with at least 10 entries (you're welcome to use fictional data - the point here is demonstrate that your DB is functional and can fulfill the needs of the client company).
Attachment:- Database Planning.rar