Assignment 4 -- Logical and Physical Design Phases for Developing a Database Application Project
Due date: November 13th, before the start of class.
Value: 40 points of 200 for Assignments.
Continue to work with your group for the Assignments and the Project required for this course.
For Assignment 4, EACH GROUP must complete all parts; the required items need be produced only once. The group continues to develop a database application.
The scope of the semester project, including the cumulative assignments, must give you an experience of enterprise-wide computing, not just a simple desktop application for one user. Some project standards/requirements exist; the database application you develop must include:
- more than one functional area (e.g., marketing and distribution, or inventory and planning, or personnel and sales, etc.);
- more than three (major) high level data (types) groupings (e.g., employees, products, locations, products, customers, positions, payments, etc.);
- more than one 1-to-many relationships between (major) high level data (types) groupings (e.g., "one location may house many employees");
- more than 3 input/output interface requirements for users and/or managers.
Note: you may not use any business/organizations and functions based on customers/orders/order details, nor business/organizations and functions based on courses/students/departments, nor business/organizations and functions based on hospitals/patients; these are business/organizations and functions used in copious sample exercises by the textbook.
For the business or organization you have chosen to develop a database management system (application), refine or improve the following deliverables you produced for Assignments 1 and 2:
- name of the business or organization;
- product, product line, or service;
- some major corporate functions;
- some business processes;
- any existing computer applications;
- some major data entity types ("high level groupings");
- some needs or problems that are to be addressed by developing a new database application;
- some ways employees and managers will use the potential new database application;
- some strategic planning factors: long term vision, some major planning goals, some critical success factors, and some problem areas;
- some corporate planning objects: organizational units, locations, some major corporate functions, and some major data entity types ("high level data groupings");
- an organization chart along functional decomposition lines;
- a preliminary data model -- a high level entity-relationship diagram;
- a formal statement of proposal for a new database application to solve a problem, meet a need, or take advantage of an opportunity -- and thus serve the strategic plan of the business or organization;
- a planning matrix showing function-to-data entity usage expected in the proposed new system;
- (optional) other planning matrices as the group sees necessary or helpful in planning: location-to-function, unit-to-function, unit-to-data entity, location-to-data entity.
- complete a detailed e-r diagram for the new database application system; include details such as all entities, all attributes (especially identifiers/keys), relationships, and upper/lower cardinalities for those entities you will develop and use;
- define at least five business rules that will impact the database; at least 2 structural and at least 2 operational; describe them clearly and in detail; specify the data entities, attributes, or relationships to which they are connected;
- depict the rules on an e-r diagram using the Ross symbol conventions.
- define in detail all business processes required to successfully support at least two business functions; only the two functions on which the group is working; use a stepwise, sequential structure: "a happens and then b happens and then c happens";
- define in detail some specific "views" of data entities and attributes needed by different persons working in the business functional areas you are supporting (i.e, to perform the processes you defined above);
- define in detail at least 3 different forms/screens/displays, complete with details for each section or block; in an accompanying narrative, tell who will use them, how they will be used and to support what processes;
- define in detail at least 3 reports, complete with details for each section of block on each page; in an accompanying narrative, tell who will use them, how they will be used and to support what processes.
Note: make certain that no data specified on a view, form, or report are not included in the data model specified.
Then produce the following new deliverables for this Assignment:
- Construct a logical schema for the relations in your dbis, using either the graphic convention used in your textbook or the one we used in class. Provisos: include at least 3 different relations, with all their attributes and all keys; include the referential integrity "blue lines" from foreign keys to primary keys; be sure to correctly transform your eer diagram into relations, as directed by the textbook's mapping "Steps 1 through 7." (Chapter 6)
- Using Access, build a phsyical design for the data components of your dbis: relations/tables, fields, keys, all field properties needed to enforce business rules via data integrity, relationships between tables, and enforcement of relational integrity. Provisos: at least 3 tables, linked by the relation/linking steps shown in class (i.e., 2 relationships); use all data types shown in class, even if you need to add new attributes/fields to enable you to do so; implement your business rules that relate to data integrity, by using all properties demonstrated in class, as appropriate for each field and data type.
- Using Access, build a physical design for the application components of your dbis: forms, reports, views/queries. Provisos: at least 3 forms, 2 of which are multi-table; at least 3 reports, 2 of which are multi-table; at least 3 views/queries, 2 of which are multi-table; implement business rules where possible; make sure that everything works correctly.
- Briefly describe how each form, report, and view/query will be used by the end-users of the business, and what improvements will be realized in business functional areas as a result of such use.
Textbook Aids. See McFadden, Hoffer, Prescott (MHP) -- references to the Pine Valley Furniture business "case":
- Chapter 6.
- Chapter 7.
- Access book of your choice.
Insert your completed, printed deliverable into your portfolio, in the fourth section of your dividers. Save your Access ".mdb" file to an otherwise empty diskette and insert the diskette into the side sleve of the cover of your portfolio; be sure it is secured. Make sure your portfolio is properly identifed with your names. Turn in your portfolio at or before the due date/time.
Fax: (504) 865-5562