[مخالف]اريد المساعدة في حل عمل دورة

Briefing

The aim of this assignment is to undertake a range of tasks involved in designing a

Database Inventory System for Micros & Machines Ltd (M&M) based on the accompanying

description that was acquired during the first interview with the firm manager.

Some of the information provided may be of little relevance, and other information,

which is required, may be missing. Where information is not available you should make

reasonable assumptions.

You are to undertake this assignment individually, although you may discuss ideas

with your fellow students. However, the final submission must be the work done by

you.

Specification

M&M is a computer manufacturer, which requires a database system for its parts

inventory. The inventory comprises all the mechanical and electronic parts M&M use

to make their computers.

Each part has a unique part number, description, and serial number. The quantity on

hand, reorder levels and re-order quantity for each part all need to be recorded in

the system. In addition, electronic parts need their power rating and voltage

recorded and mechanical parts their physical dimensions.

When more parts are required, authorised M&M employees raise purchase orders

with their suppliers. All the items on the purchase order are recorded on the system,

i.e. the employee’s name, purchase order number and date, supplier's name and

address, parts ordered with their quantity and unit price. The date the parts are

required may also be recorded along with the delivery charge (i.e. postage &

packaging) and the delivery date (i.e. despatch or shipping date).

Note that the same part may be available from different suppliers and the unit price

of the part depends on the supplier it comes from and the date it was ordered. Full

contact details and payment terms on the suppliers are kept on the system. If a

supplier stops supplying parts to M&M, its details are no longer required on the live

system, but the details are archived at the end of every year. The details are then

kept for one year and then deleted permanently after that.

For simplicity, you may assume that M&M use the same part numbers as their

suppliers, and a part number is unique among all suppliers.

The parts may have other parts as components and/or they may themselves be

components of other parts. For instance, a motherboard part M2002 has various

different component microchips on it: C8080, C8090 etc; and a particular chip, say

CW_COMP1302_156713_ver1_0809 Page 3 of 6 printed on 31-Jul-09

the C8080, may itself be component of many different circuit boards made by the

company: M2001, M2002 etc. Note that a component can have a maximum of three

levels of subcomponents.

M&M management has decided that rather than removing suppliers who supply no

parts from the DB on an annual basis, this should be done immediately after the

supplier stops supplying parts, on a weekly basis. All archived data (i.e. details of

supplier details and records of all supplies) is to be kept.

Sample Applications

Initially, the following applications for the system are planned, but please note the

list given below is only a sample:

A1. A list showing the names and addresses of all suppliers of electronics parts; the

list should also include the total number of electronics parts that is currently

supplied by each supplier. Produce the SQL code only (i.e. no form or report).

A2. Produce a list of all archived suppliers and the date of the last order that they

supplied to M&M. Produce the SQL code only (i.e. no form or report).

A3. For both Electronics and Mechanical components, list the total number of

components under each category and the total number of suppliers that supply

those components under each category. Produce the SQL code only (i.e. no form

or report).

A4. List all the parts required for a particular motherboard. Produce the SQL code

only (i.e. no form or report).

A5. For any given supplier name give the total number of delivered orders and the

total amount of that order, after a given date. Both, the supplier name and the

date should be picked from a drop down list by the user at run-time. Produce

the output on a report.

A6. A list of all purchase orders that are raised by a given employee after a given

date. The form should allow the M&M manager to choose an employee name, and

view all details of purchase orders that were raised by that employee after a

given date. The name of the employee and the date should be captured at runtime

from a drop-down list by the user. The output is to be in a master/detail

form.

CW_COMP1302_156713_ver1_0809 Page 4 of 6 printed on 31-Jul-09

Deliverables:

D1. One A4 page, state clearly any assumptions (i.e. Enterprise or Business rules) that you make

about the data; in particular, noting any information that you believe should be included, but

is not mentioned in the outline specification.

D2. One A4 page containing the conceptual data model diagram (i.e. an Entity Relationship

Diagram, using the Chen notation) for the system. Your diagram should show:

• Relevant Entity Type,

• You only need to show the following attributes in the conceptual model: a Primary Key for

each entity; any multi-valued or derived attributes; Derived attributes & Relationships

attributes1.

• Relationship Type with a role name (plus relationship attributes if any),

• Structural constraints for each relationship (both cardinality and participation).

Note: if you show attributes other than the Primary Keys (e.g. Foreign Keys) then you will be penalised.

D3. For the above model, produce a relational schema (i.e. transforming the conceptual data

model into a logical relational schema) on one A4 page – DO NOT show the mapping steps.

Your Relational Schema should show:

• All entity and relationship types that are potential for a relational table.

• For each potential table identify the primary key and any necessary foreign keys and all

other attributes.

• Show the links between the tables2 (e.g. a table-relationship diagram produced by MS

Access) or, for each table, describe any links to other tables in SQL statement(s).

D4. Normalisation check: You need to check your produced Relational Schema above for 3rd NF.

If it satisfies 3NF criteria then you ONLY need to include the statement “The Relational

Schema satisfies 3NF criteria”. If your schema does not satisfy the criteria of 3NF then you

need to reproduce your schema in 3NF. You DO NOT need to show the steps (i.e. process) of

Normalisation.

D5. Create a DB for the above schema in an appropriate relational DBMS and populate each table

with typical records to clearly demonstrate the application results. You DO NOT need to

produce a snapshot of the tables. Please note that all students within a cohort must use the

same DBMS as directed by your tutor.

D6. Write the SQL code only (i.e. no form or report) for applications A1-A4 above. —DO NOT

USE the QUERY BUILDER tool available with your DBMS. Code produced automatically

by the wizard (i.e. tools) will be awarded ZERO.

1 To avoid cluttering the Conceptual Data Model with many attributes and to improve clarity, other attributes can be

shown in the logical model (i.e. or listed in the relational schema). Any extra attributes which are not required to be

shown on the CDM (as indicated above) will be ignored. Please note that presenting Foreign Keys at the CDM

diagram is wrong and students will be panelised if they do so.

2 You can show the links between Primary and Foreign keys on your relational schema as arrows starting from the FK

and ending at the PK, as shown in the example below.

Doctor (DocNo, DocName, BirthDate, Address, City, PostCode, Tel_H);

Doctor_Qualification (DocNo, QualName, QualDesc, DateAwarded); /*DocNo referencing Doctor.DocNo */

CW_COMP1302_156713_ver1_0809 Page 5 of 6 printed on 31-Jul-09

D7. Using the appropriate tool from the chosen DBMS, create a form for registering new supplier.

This should have two buttons on it— one to commit a new supplier to the Database and one

to exit the form. A screen dump of this application at run-time is required.

D8. Using the appropriate tool from the chosen DBMS, produce a report for application A5

above.

D9. This is for application A6 above. Using the appropriate tool from the chosen DBMS create a

master/detail form to perform this application.

D10. You are required to implement all above applications as well as submitting a one A4 page

containing the SQL statements only required by the sample applications above. You need to

test all your queries, forms and reports with sample data and make sure that all your

applications produce some answers. You will be asked by your tutor, during your demo, to

run some/all of the applications.

Your electronic submission should be in two files:

ONE PDF document consists of followings:

1. One A4 page for any assumptions and business rules. Ref. D1

2. One A4 page for Conceptual Model. Ref. D2

3. One A4 page for the mapped Relational Schema (i.e. Logical Data Model) showing

links between Foreign Keys & Primary Keys. Ref. D3

4. One A4 page for Normalisation declaration (or a reproduction of the relational schema

in Third Normal Form). Ref . D4

5. One A4 page for presenting SQL code for the required applications. Ref. D10

6. Screen dumps for all required applications in D6 and One Screen dump for each of

deliverables D7-D9.

ONE Zip file containing all DB files (e.g. Microsoft Access DBMS or any appropriate relational

DBMS3, SQL servers files, Visual studio source file, etc.). Ref. D5

3 Using Oracle, Access or SQL server, etc. makes no difference for achieving the aims of the coursework. Oracle users

should submit the following: a file containing all SQL scripts that are used in creating and/or populating the database

objects; a file containing the SQL scripts used to answer the sample applications, all oracle forms and reports files

(i.e. …… .fmb and .rdf files).

CW_COMP1302_156713_ver1_0809 Page 6 of 6 printed on 31-Jul-09

Grading Criteria

Note: Students Will Fail the Coursework If:

1. Failure to attempt an implementation regardless of the quality of the design,

2. Do not demonstrate the work to their tutor, or

3. Having very poor implementation. The marks will be capped as explained below.

• If the student is unable to produce a single fully operational application correctly, then the

maximum awarded mark is 20%.

• If the student completes only one fully operational application correctly and attempted most of

the others then the maximum awarded mark is 40%.

• If the student completes two or more fully operational applications correctly then cswk is

marked according to the marking schema below with no capping.

Conceptual Model 40%

Many possible models, but the model should identity major entities and relationships.

• Identifying Entities with a proper unique identifier: 15%

• Reasonable Assumptions: 5%

Assumptions are to clarify unclear business rules or procedures. Assumptions are to reflect

common-sense and student’s ability to make the right judgement when information is

missing. Any unreasonable assumption that aims to make the design simpler and

compromise or ignore business rules gains no marks.

• Identifying Relationships, Generalisation/Specialisation hierarchies 10%

• Cardinality and Participation Constraints of relationships 10%

Relational Schema (i.e. Logical Model) 20%

• A list of candidate attributes for each table as specified in the requirements. 5%

A PK may be identified by an underline (a common practice) and a FK by any other

means as described by the candidate (e.g. double underline, italic style, different colour

font, etc.).

• Produced Relational Schema (mapping): 10%

The focus here is on the link between Primary keys (PKs) and Foreign Keys (FKs). Use

the standard notation for the relational schema and draw arrows to show the links between

the tables. The arrows should originate from the FK attribute and end at the referencing

attribute as shown below:

<table1Name> (PK Identifier, attribute1, attribute2, FK_attribute, etc.);

<table21Name> (PK Identifier, attribute1, …, FK_attribute, etc.);

Points to check: placements of FKs in correct target table(s), need to create new

tables, and correct handling for any superclass/subclass constructs.

• Normalisation Check 5%

The produce logical schema is expected to be in 3rdNF. A candidate may normalise their

tables to produce the 3rdNF version, or (if the schema in 3rd NF, an assurance statement

that the produced relational schema is in 3rdNF is essential).

Implementation and SQL Applications 35%

• A candidate should implement the Database on the target DBMS and populate the

each table with sample data (3-5 records each). 5%

• Implementing the correct constraints (PK/FK links, Null allowed or not, etc.)

• Implementing the above applications as specified (e.g. producing SQL code, passing

parameters, etc.). 30%

Quality of presentation/demonstration 5%

• Presentation of your screens (i.e. navigation) and reports; quality of your

deliverables (Conceptual and logical relational model, assumptions, SQL code, etc.). This