Database Design and Development Assignment Help

Qualification - Higher National Diploma in Computing

Unit Name - Database Design and Development

Unit Number - Unit 4

Assignment Title - Database Design Double Glazing

Learning Outcome 1: Use an appropriate design tool to design a relational database system for a substantial problem.
Learning Outcome 2: Develop a fully functional relational database system, based on an existing system design.
Learning Outcome 3: Test the system against user and system requirements.
Learning Outcome 4: Produce technical and user documentation.

Assignment Brief: You should design and develop a relational database system to solve a problem of your own choosing. The case study, below, can be used if you are unable to find your own problem.

Case study
You are employed as a Database Developer for a large IT consultancy company. The company has been approached by a double glazing company called Double Pane which is expanding rapidly due to the huge growth in demand of their products and services from building contractors. New housing developments are growing as a result of the government's agenda to regenerate the old disused factory complexes outside the city of Bedford Falls.

Double Pane have, to date, managed their business by using a computerised system which was developed by a college student five years ago. The system is now too slow and no system documentation was produced when it was implemented so changes to the system cannot be made easily. The CEO of Double Pane feels that their business requirements have changed, and they need to invest in new technology and a new system in order to grow and manage their business successfully.

Double Pane currently has sales agents who follow up sales leads which are mainly generated from their marketing material and calls taken by staff in the company office. Sales are also generated by the agents who visit building contractors. Each visit from a lead results in a quote and some of these quotes result in an actual sale, either to supply only, or to supply and install.

Double Pane supplies a huge range of windows and doors. They buy most of their products in bulk from a few large manufacturers and store the products in their warehouse. They also make some bespoke windows and doors in their workshop in the warehouse.

Your company has accepted the contract to develop a database system for Double Pane and to provide the necessary technology to run the system. Your brief is to develop the database system element of the contract from Double Pane.

You have been asked by your manager to investigate the system requirements; and design, create and implement the system. One of the requirements of the contract is that user and technical documentation be made available to Double Pane.

Logical Data Model for A Double Pane Management System

Executive Summary

The objective of this report is to design an sales inventory system that is for Double Pane company.

DOUBLE PANE company need a computerized system that will maintain the inventory and sales purchase. we are a Database Management System. DOUBLE PANE company new database system will handle the items and stockroom employee and sales employee. Then the database model working will smooth running of sales operation and stock management.

The first step of database model design after the study and analyzed is design conceptual database design.
Conceptual Database DesignMethodology

In this report first we would design the conceptual database design process with given below steps.

Identifying entitytypes

According to company case study there are two types of employee ; stockroom employee and sales employee. In database design first step is entity is identifying classifying the entity.
According to case study; our database have three main entities
Identifying stockroom entities
Stockroom entities:-
Stockroom employee, items, suppliers, Sales_Invoices
a. Identifying sales entities
Sales entities:-
Sales employee, items, Clients, Sales_Invoices, OrderPayment_Receive

b.Entity types

Data Dictionary

Hire British tutors and access our Level 5 assignment assistance for Unit 7 Business Strategy focusing on TESCO. Engage with our UK-based tutor service for expert guidance.

Part 1

Before you start the development process, your manager has asked you to produce a report for the CEO of Double Pane, containing:

1. The design of the relational database system using appropriate design tools and techniques. It should contain at least four interrelated tables.
2. Clear statements of user and system requirements.

You would prefer to produce a more detailed document, so you will produce a comprehensive design for a fully functional system which will include interface and output designs, data validations and cover data normalisation.

Your manager would like a separate report on your assessment of the effectiveness of the design in relation to user and system requirements.

Identifying relationship types

The relationship between the attributes will be different .
1. Double Pane has stockroom and salesemployee
2. Sales employee is manage by Sales manager
3. Sales Sales_Invoices is receive to Sales employee
4. Sales Sales_Invoices has manage according to Sales_Invoices id and Sales_InvoiceingclientNo, Client name.
5. Sales Sales_Invoice contain complete items information.
6. Items has item ID, item name, item itemprice
7. Item belongs to types
8. Types has TYPE id TYPE id
9. Each TYPE item has suppliers
10. Suppliers has supplier ID, supplier name
11. Items are stored in stockroom
12. Stockroom is managed by stockroom employee

Avail the best HND Assignment Help and Assessment Writing Services at affordable prices!!

Part 2

Once the designs have been accepted by your manager you have been asked to develop the database system using evidence of user interface, output and data validations and querying across multiple tables.

You want to include more than just the basics so you will implement a fully functional database system which will include system security and database maintenance features.

You have decided to implement a query language into the relational database system. The developed system will be demonstrated to your manager.
Your manager has asked you to produce a report:

1. Assessing whether meaningful data has been extracted through the use of query tools to produce appropriate management information.

2. Evaluating the effectiveness of the database solution in relation to user and system requirements, and suggest improvements.

ER diagram

Employee
Employee ID, FName, LName, DOB, Address, role ID, phone No, loginname, password
Client
Client ID, FName, LName, Address, phone, Email, BusinessRegNo,
Sales_Invoice
Sales_InvoiceID, Invoice_number, Employee ID, ClientID, O_Date, DispatchedDate, OrderPaymentID, ErrorMsg, Deleted, Paid,
Sales_Invoiceinformation
Sales_InvoiceId, Invoice_number, ItemId, Itemprice, OrdQuantity, DelQuantity, Discount, Total, Size, InvDate, DispatchDate, Sales_InvoiceDetailID, BillDate
OrderPayment
OrderPaymentID, OrderPaymentType, CreditAmount, DebitAmount, Balance, BalanceDate,
Item
ItemID, ItemName, ItemDetail, Supp_ID, TYPEID, QuantityPerUnit, UnitItemprice, UnitWeight, Size, Discount, UnitsInStock, UnitsonSales_Invoice, ReSales_InvoiceLevel, ItemAvailable, Curr_Sales_Invoice,Description
TYPE
TYPEID, TYPEName, Detail,
Supplier
Supp_ID, SName, ContactFname, ContactLname, ContactName, Address, Phone, Fax, Email, OrderPaymentMethods, DiscountType

Acquire instant solutions by hiring our Level 4 Principles of Health Social Care Practice assignment help service!

Attributes and Entities

Grab the Best Opportunity To Save High Marks With Unit 4 Database Design and Development - Higher National Diploma in Computing Assignment Help!!

Primary, and Alternate keys

Those attributes which can uniquely identify entire entity are the candidate keys. One of these attributes shall be the primary key and the other keys are alternatekeys.

Entities with candidate keys, primary key and alternate keys

Logical Database Design

Deriving relations for the logical data model

Relations in Database Definition Language (DBDL)

a. Employee hasrole
Employee (Employee ID, FName, LName, DOB, Sex, address, phone, loginname, password, Role ID)
PrimaryKey EmployeeID
AlternateKey loginname
Foreign Key R_idrefrencesRole (Role ID) Role (Role ID, rolename,Detail)
PrimaryKey Role ID

b. Employee registers Clients
Client (TID, SRegistedNo, fname, lname, address, phone, email, employeeID)
PrimaryKey TID
AlternateKey SRegistedNo
Foreign Key employeeIDreferences Employee(employeeID)

c. Client makesSales_Invoice
Sales_Invoice (OderID, O_Date, ErroMsg, employeeID, TID)
PrimaryKey Sales_InvoiceID
Foreign Key employeeIDreferences Employee (EmployeeID)
Foreign key TID references Client(TID)

d. Sales_Invoice has Sales_Invoicedetails
ODetail (OdetailID, unititemprice, size, Quantity, discount, total, DDate, billDate, Invoice_number, Sales_InvoiceID, ItemID)
PrimaryKey OdetailID
Foreign Key Invoice_number references OrderPayment (Invoice_number) Foreign Key Sales_InvoiceID references Sales_Invoice (Sales_InvoiceID) Foreign Key ItemID references Item(ItemID)

OrderPayment (Invoice_number, payType, CrAmount, CrDate, DrAmount, DrDate, Balance)
PrimaryKey Invoice_number

Item (ItemID, INameIDescrQperUnit, Uitemprice, USP, Uweight, Usize, Discount, UInstock, UInSales_Invoice, ReSales_InvoiceLevel, Description, Supp_ID, TYPEID )
PrimaryKey ItemID
Foreign Key Supp_IDreferences Supplier (Supp_ID)
Foreign Key TYPEID references TYPE(TYPEID )

e. Item belongs toTYPE
Item (ItemID, INameIDescrQperUnit, Uitemprice, USP, Uweight, Usize, Discount, UInstock, UInSales_Invoice, ReSales_InvoiceLevel, Description, Supp_ID, TYPEID )
PrimaryKey ItemID
Foreign Key Supp_IDreferences Supplier (Supp_ID)
Foreign Key TYPEID references TYPE(TYPEID )

TYPE (TYPEID , TypeName, Detail)
PrimaryKey TYPEID

f. Supplier supplies item
Supplier (Supp_ID, comName, confName, conLname, conName, Address, Phone, fax, Email, PayMeth, DiscType)
PrimaryKey Supp_ID

Item (ItemID, INameIDescrQperUnit, Uitemprice, USP, Uweight, Usize, Discount, UInstock, UInSales_Invoice, ReSales_InvoiceLevel, Description, Supp_ID, TYPEID )
PrimaryKey ItemID
Foreign Key Supp_IDreferences Supplier (Supp_ID)
Foreign Key TYPEID references TYPE(TYPEID )

Relations usingnormalization
Our next step is normalization ;This process helps in reducing of data redundancy and solving update anomalies.Hence we are doing normalization of data up to 3NF.

Referential integrity constraints

Employee (Employee ID, FName, LName, DOB, Sex, address, phone, loginname, password, Role ID)

PrimaryKey                                                                                    EmployeeID

AlternateKey                                                                                   loginname

Foreign Key                                                                                     R_idreferencesRole (RoleID

ON UPDATE CASCADE ON DELETE SET DEFAULT (SALESMEN ROLE)

Role (Role ID, rolename, Detail)PrimaryKey              Role ID

ON UPDATE CASCADE ON DELETE NO ACTION

Client (TID, SRegistedNo, fname, lname, address, phone, email, employeeID)

PrimaryKey                                                                                    TID -> ON UPDATE CASCADE ON DELETE NOACTION

AlternateKey                                                                                   SRegistedNo

Foreign Key                                                                                     employeeIDreferences Employee(employeeID)

ON UPDATE CASCADE ON DELETE SET TO DEFAULT (SALES MANAGER)

Sales_Invoice (OderID, O_Date, ErroMsg, employeeID, TID)

PrimaryKey                                                                                    Sales_InvoiceID -> ON UPDATE CASCADE ON DELETE NO ACTION/CHECK

Foreign Key                                                                                     employeeIDreferences Employee (Employee ID) ON UPDATE CASCADE ON DELETE SET TO DEFAULT (SALES MANAGERID)

Foreign key                                                                                      TID references Client (TID) ON UPDAGE CASCADE ON DELETE NOACTION

ODetail (OdetailID, unititemprice, size, Quantity, discount, total, DDate, billDate, Invoice_number, Sales_InvoiceID, ItemID)

PrimaryKey                                                                                    OdetailID ON UPDATE CASCADE ON DELETE NO ACTION

Foreign Key                                                                                     Invoice_number references OrderPayment (Invoice_number) ON UPDATE/DELETE NOACTION

Foreign Key                                                                                     Sales_InvoiceID references Sales_Invoice (Sales_InvoiceID) ON UPDATE/DELETE NOACTION

Foreign Key                                                                                     ItemID references Item (ItemID) ON UPDATE CASCADE ON DELETE NOACTION

OrderPayment (Invoice_number, payType, CrAmount, CrDate, DrAmount, DrDate, Balance)

PrimaryKeyInvoice_number ON UPDATE/DELETE NO ACTION/CHECK

Item (ItemID, INameIDescrQperUnit, Uitemprice, USP, Uweight, Usize, Discount, UInstock, UInSales_Invoice, ReSales_InvoiceLevel, Description, Supp_ID, TYPEID )

PrimaryKey                                                                                    ItemID ON UPDATE/DELETECASCADE

                                                                                                            (Supp_ID)            ON UPDATE/DELETECASCADE 

Foreign Key                                                                                     Supp_ID               references             Supplier

Foreign Key                                                                                     TYPEID references TYPE (TYPEID ) ON UPDATE/DELETE CASCADE

Type (TypeID, catName, Description)

PrimaryKey                  TYPEID ON UPDATE/DELETECASCADE

Supplier (SupplierID, comName, confName, conLname, conTitle, Address, Phone, fax, Email, TypeINFO)

PrimaryKey                  supplierID ON UPDATE/DELETECASCADE

Level up with the Level 5 Diploma in Hospitality Management's Unit 2: Finance in the Hospitality Industry.

Part 3

Once the system has been developed, you will test the system and your manager will complete a witness statement indicating how your tests are performing against user and system requirements.

You will produce a brief report assessing the effectiveness of the testing, including an explanation of the choice of test data used.

Lastly you will produce technical and user documentation which will be given to the company.

You want to provide some graphical representations for ease of reference in the technical guide, so you have decided to produce a technical and user documentation for a fully functional system, including diagrams showing movement of data through the system, and flowcharts describing how the system works.

Double Pane Propose Data Model Query

Since Microsoft Access is best suitable for our assignment .We design and develop an Access base database application The application having numerous feature that will help to generate report for inventory and sales.

Conclusion
In this report ,we design logical data model that has been produced during the project is fully capable of defining the data requirements of the Double Pane company. During the analysis the case study of company, all the data requirements, functional requirements were closely observed and accordingly data model prepared;This data model ready for physical implementation of the DOUBLE PANE SYSTEM database system.

Get the top-notch Unit 28 Work-based Experience Assignment Help service for Level 5 business courses at cost-effective rates!

Presentation

Executive Summary

Double Pane Relation data base Model
The aim of this assignment is study and analysis the case study of The DOUBLE PANE company need a computerized system that will maintain the inventory and sales purchase. We are proposing the solution for the Double Pane company according to given scenario. After the analysis we reach out the solution for the company; we will prepare a relational data base model .

The proposed relational data base model will capable for handle the items and stockroom employee and sales employee.

The first step of database model design; is conceptual database design therefore we will design the conceptual database design with given below step:

Identifying entity types
According to company case study there are two types of employee ; stockroom employee and sales employee. In database design first step is entity is identifying classifying the entity.
According to case study; our database have three main entities
Identifying stockroom entities
Stockroom entities:-
Stockroom employee, items, suppliers, Sales_Invoices

our database have three main entities
Identifying stockroom entities
Stockroom entities:-
Stockroom employee, items, suppliers, Sales_Invoices
Identifying sales entities
Sales entities:-
Sales employee, items, Clients, Sales_Invoices, OrderPayment_Receive

Identifying relationship types
The relationship between the attributes will be different .
Double Pane has stockroom and sales employee
Sales employee is manage by Sales manager
Sales Sales_Invoices is receive to Sales employee
Sales Sales_Invoices has manage according to Sales_Invoices id and Sales_Invoiceing clientNo, Client name.
Sales Sales_Invoice contain complete items information.

Items has item ID, item name, item itemprice
Item belongs to types
Types has TYPE id TYPE id
Each TYPE item has suppliers
Suppliers has supplier ID, supplier name
Items are stored in stockroom
Stockroom is managed by stockroom employee

Relations in Database Definition Language (DBDL)
 Employee has role
Employee (Employee ID, FName, LName, DOB, Sex, address, phone, loginname, password, Role ID)
Primary Key Employee ID
Alternate Key loginname
Foreign Key R_idrefrences Role (Role ID) Role (Role ID, rolename, Detail)
Primary Key Role ID

Employee registers Clients
Client (TID, SRegistedNo, fname, lname, address, phone, email, employeeID)
Primary Key TID
Alternate Key SRegistedNo
Foreign Key employeeID references Employee (employeeID)
Client makes Sales_Invoice

Sales_Invoice (OderID, O_Date, ErroMsg, employeeID, TID)
Primary Key Sales_InvoiceID
Foreign Key employeeID references Employee (Employee ID)
Foreign key TID references Client (TID)
Sales_Invoice has Sales_Invoice details
ODetail (OdetailID, unititemprice, size, Quantity, discount, total, DDate, billDate, Invoice_number, Sales_InvoiceID, ItemID)
Primary Key OdetailID
Foreign Key Invoice_number references OrderPayment (Invoice_number) Foreign Key Sales_InvoiceID references Sales_Invoice (Sales_InvoiceID) Foreign Key ItemID references Item (ItemID)

Relations using normalization
Our next step is normalization ;This process helps in reducing of data redundancy and solving update anomalies. Hence we are doing normalization of data up to 3NF.
In this report we create a database ; that normalization of data up to 3NF.

CONCLUSION
In this report ,we design logical data model that has been produced during the project is fully capable of defining the data requirements of the Double Pane company. During the analysis the case study of company, all the data requirements, functional requirements were closely observed and accordingly data model prepared; This data model ready for physical implementation of the DOUBLE PANE SYSTEM database system.