DATABASE MANAGEMENT & PROGRAMMING ASSIGNMENT

I need imidiate help for this project!

I am a computer science student here in Cyprus but I don’t have a clue what its happening over here.

Is it possible for anyone to help me????

Please contact with me at my e-mail [email protected]

If you are willing to help me

Thank you all in advance

·         The project should be implemented using SQL commands that could be issued from Microsoft Access, or ORACLE Product SQL*PLUS or some other SQL environment.            

Deliverables:

The list of delivables to be handed in (both hard copy and electronic copy) on or by the hand-in date is as follows:

       The Case Description.  In 2-5 pages produce a description of the case under study along with the main user requirements.  Expand the case given by suggesting queries and other system requirements which you deem useful to end-users.

       The database model (Entity Relationship Model plus supporting narrative, including any assumptions made).  For the design of the ERM you should use an appropriate CASE tool and any one of the notations covered in the course.

       A Relational Schema, which comprises listings of table definitions clearly identifying primary keys (underlined) and foreign keys (in italics).  

       The code and results of data definition and manipulation (i.e. table creation, data insertion and the required SQL queries) including the appropriate test data.

WoodWorks Ltd. is a wood products manufacturer in Cyprus. WoodWorks specializes in manufacturing all kinds of high-quality furniture components, adapted to the specific needs of individual customers (tailor-made).

WoodWorks’ product range includes “Root-like WoodWorks” (furniture components manufactured from wood layers, surfaced on chipboard, MDF or plywood). It also includes molded plywood, and other woodworking, based on customers’ own designs and specifications. WoodWorks’ products are delivered in a semi-finished form and are used where high-quality requirements is a must. Furniture manufacturers and carpenters use them in the production of their own specialized woodworks.  Architects, interior designers, and specialized furniture manufacturers also use “Root-like WoodWorks” to make superior quality furniture and decorations. WoodWorks’ products have several specialized applications for the production of exclusive products, including decorative frames, furniture parts, hotel, restaurant and bank decorations, shop windows, dinner table tops, office worktops, kitchen cabinets etc.

WoodWorks buys most of its raw materials from its mother company. In this way it manages to keep a relatively small stock of raw materials, thus having a cost advantage. All prices of raw materials are determined in accordance with a special agreement (cost plus system) between the two companies. WoodWorks also buys a small part of its raw material requirements from sources outside the group.

WoodWorks’ production process is initiated when a customer places an order by supplying the company with his/her product design, choice of wood to be used, dimensions and other specifications.  The products ordered by individual customers normally differ in size, type of surface (wood layer or melamine), basic material (chipboard, MDF or plywood), finish, etc.  Because of these specific characteristics of the orders, each order’s price is calculated individually.  Obviously, the need arises for a quick and reliable costing system in order to facilitate the management of the company to quote the correct price to the customer, to estimate the profitability of the operations, and to help the pricing policies of the company.  The current system is a manual one handled by the company’s production manager who makes tentative cost estimates of the main production elements, i.e., raw materials, labour, plant usage and other operation costs. Subsequently, depending on this costing, the manager gives a price to the customer who decides whether to place the order or not.

The top management of the company suspects that there might be a considerable degree of inaccuracy in this system, and this may cause serious problems to the business profitability, endangering the company’s long-term viability and its market position vis-à-vis existing competition from other local or foreign suppliers.  They have therefore requested that we implement an automated production costing system.

For each order the following information will be recorded:

1. Raw materials’ content - Types, quantities and costs of the materials required for the production of the specific order.
2. Labour content - Number of workers, number of man-hours and hourly rates involved.
3. Plant contents - Types of machines, machine hours and hourly machine costs required.
4. Overhead contents - An additional percentage on top of the previous categories reflecting the overhead costs of the company, which cannot be directly allocated to any of the three categories above.

The system should initially keep the cost estimation and quotation given to the customer and then the placement of an order, if the customer decides to proceed with one. It should also offer the possibility of monitoring actual costs after the completion of an order, for reasons of verification, comparability and gradual improvement of its accuracy. The system will provide valuable information to the top management for reasons of cost monitoring, price quotations and marketing policies.

Data should be maintained about employees, customers and suppliers. The system should also keep track of products, and available inventory, customer requests, quotations, and orders from customers and of course purchases from suppliers. For the completion of the product quotation there should be input made concerning employee man-hours, machinery usage and raw materials needed in order to calculate the total cost of the specific order. The system will then add a percentage for overhead costs to get the total cost. The involvement of specific employees (of different specializations) in the execution of each order will be recorded so as to know the task(s) assigned, at first, and then completed by each employee.  After an order has been executed, the user should add the actual cost of the specific order to the system, in order to compare actual costs with estimated costs.

(Case created using data about Xyloform Ltd., a subsidiary of Cyprus Forest Industries Public Ltd.  http://www.cfi.com.cy/index.php )

Requirements for the case study (deliverables in detail)

1.       Develop an entity model to support the above scenario.  You may be required to make some assumptions.  Please state and explain these in your documentation. 

Your model should comprise:

                          I.            An E-R diagram clearly showing the maximum and minimum cardinality for each pair of related entities.                                                                                       (30 marks)

                        II.            A normalised relational schema derived from the E-R diagram including appropriate attributes associated with each entity, clearly showing the primary key of each relation and the foreign key where applicable.  A data dictionary describing the type, size and meaning of each data element or field is also required.                                              (20 marks)

Note:

·         You may make suitable and intelligent assumptions.

·         Anything that is not stated as part of the requirements may be assumed.

·         Your assumptions must NOT override any user requirement.

·         All assumptions must be clearly stated or explained in your report.

2.       Set up tables, including well-designed test data, to implement the application, and identify appropriate integrity constraints to help ensure that data is entered with correct values.  You must make sure that your sample data covers all requirements of the queries in section 3 below.  Therefore queries that yield null results/ output will not gain full marks even if the SQL code is correct.  The code for creating and inserting data into the tables/relations must be provided.                                                                                                                                                                      (20 marks)

3.       Suggest meaningful end-user queries to extract data from your database. Set up and test all queries using an SQL product. Describe each query and provide both the SQL code and the output of the query in hard copy and on disk.                           (3 marks each- Total marks: 30 marks)

You are required to describe and then satisfy a minimum of 10 queries extracting data from your database.  These queries should vary from simple (single-table, one or more search criteria) to more advanced (multiple-table, using join, subqueries, sorting, functions, views, etc.).