Database Design and Development

Database Design and Development

General Guidelines

The work you submit must be in your own words. If you use a quote or an illustration from somewhere you must give the source.

Case Study: Developing Online Shop Database for a Fashion Outlet Expansion

A fashion outlet in London decided to have an online presence for good completion and increased profit by creating an eCommerce website. The company wants to create a database system for fashion sales (regular items) and rentals (costume and decoration items). There are several categories of fashion items for sale or rent. The developed website is too connected to a database system to provide efficient storage, easy transaction, and to avoid staff errors.

Data must be maintained about customers, orders, items, payment, and employee details. In one sale customers are allowed to buy or rent any number of fashion items. The items in the sale can only be brought by one account and the cost of the items brought are only made to one payment card.

A customer is provided with an online shopping basket where the items to be purchased are placed. As each item is selected the item is placed in the shopping basket. When the customer has completed their shopping the customer is invited to check out and pay for their items in the shopping basket.

At the checkout, the card details are verified. If successful, an email is sent to the customer with details of the sale.

As a junior database developer who recently graduated from BTEC L4 College in London, you have been recruited by the fashion outlet in London to help them develop a database for supporting and effectively managing the operations of the online website.

Your duties include the following:

Initial discussions with fashion outlets to produce the user and system requirements.

Design the database from the user requirements indicating the necessary keys and relationships. Draw an ERD diagram for the entities along with their attributes such that the database is in the third normal form (Apply 1NF, 2NF, and 3NF).

Develop the database system from the user and system requirements with a database management system.

Summary: Design a relational database that is capable of maintaining Customer Details, Product Details, Order Details, Payment Details, and the Categories details.

Database Design and DevelopmentUse an appropriate design tool to design a relational database system for a substantial problem

It is necessary to explain any assumptions made for the user and system requirements. It is important to show the different components of the case study data that illustrate the logical structure of the tables that makes up the database. In this view, you are required to illustrate the data structures and relationship of the tables extracted by designing the Entity Relational Diagram.

Produce at least 4 entities for the ER Diagram, with the attributes of the entities. Show the relationships between the entities, highlighting the primary keys, foreign keys, and constraints.

Produce a data dictionary of the logical structure of the tables, indicating the data types, the primary keys, and foreign keys.

The aim of normalization is to reduce duplications. You are to produce a well-normalized database up the third Normal Form following your listing specifically identifying the primary and foreign keys.

Based the user and system requirements, suggest a user interface design for the database system. The effectiveness of database design is usually assessed through testing. Assess your database design in relation to the user and system requirements.

 Develop a fully functional relational database system, based on an existing system design

After a successful database design, the next step is to develop the database. Develop the database system using suitable tools (SSMS or structured query language) from the ERD you have created. Your entities from the ERD, the primary keys, and foreign keys relationships should reflect the tables you have developed.

Using your design as a guide, develop your database by all the tables using Structured Query Language with necessary attributes and declare primary and foreign keys where necessary. Ensure your implementation is justified to meet user requirements. The tables created must be populated with records of at least four (4) entries for each table and the connections between the tables should confirm referential integrity.

To enhance your understanding, ensure to consider the ERD diagram below to implement querying the database.

Query the database to extract the following:

  1.   list all employees with their full names, gender, and salary
  2.   Title of all employees and their department name.
  3.  Full names and gender who belong to department number ‘d004’.
  4.  show all departments and their department’s managers.
  5.  statement to show a list of department’s managers who were hired after 1986
  1.   Change any employee’s date of birth. Assume the employee has just phoned in with his/her last name.
  2.  Delete employee’s record who belongs to department ‘d004’ and ID is 10003.
  3. Create a database view to list the full names of all employees, their departments, and their salaries.

Note that it is mandatory to provide the SQL statement and show the output from the query in the form of screenshots.

Due to the data-specific nature of databases, it is important that they are secured and maintained. To reflect your understanding of database security and maintenance, you are required to assess and evaluate how these are ensured in your implementation of the fully functional database system in accordance with users and the system’s requirements. Suggest how to better write or structure the languages in future use.

Department of Information Technology

 Test the system against user and system requirements.

It is necessary to test the database and in the process of successfully carrying out testing, a test plan suffice. In your report, outline how the system has been tested against users and the system’s requirements. This test plan preferably to be in a table format illustrating at least six (6) records tested. Ensure to have “Test Description”, “Expected Outcome”, “Actual Outcome” as headings. The “Actual Outcome” heading should include a visual representation such as screenshots of results and annotations.

From the test plan created, you are to explain the different database testing techniques and assess with evidence, one of the testing techniques implemented on your database development. You are required to implement and test the verification and validation process with the above query transaction from the database illustrating the understanding of the various features of SQL (update, sorting, joining tables, conditions using the where clause, grouping, set functions, sub-queries, etc.). In your report, including recommendations on how you can improve your database development.

 Produce technical and user documentation

Documentation helps in understanding the concept of database development. To reflect your understanding of technical and user documentation, you are required to produce full technical and user documentation for your designed database for the college. Your documentation should include diagrams showing the movement of data through the system, and flowcharts describing how the system works.

Enhancing database development is paramount in completing the development cycle. You are required to assess any future improvements that may be required to ensure the continued effectiveness of the database system.

Relevant Information

To gain a Pass in a BTEC HND Unit, you must meet ALL the Pass criteria; to gain a Merit, you must meet ALL the Merit and Pass criteria; and to gain a Distinction, you must meet ALL the Distinction, Merit, and Pass criteria.

  1. Preparation guidelines of the Coursework Document

  1. All coursework must be word-processed.
  2. Document margins must not be more than 2.54 cm (1 inch) or less than 1.9cm (3/4 inch).
  3. Font size must be within the range of 10 points to 14 points including the headings and body text.
  4. Standard and commonly used typefaces such as Times New Roman or Arial etc. should be used.
  5. All figures, graphs, and tables must be numbered.
  6. Material taken from external sources must be properly refereed and cited within the text using Harvard standard
  7. Do not use Wikipedia as a reference.
  8. The word limit must be strictly followed.
  1. Plagiarism and Collusion

Any act of plagiarism or collusion will be seriously dealt with according to the regulations. In this context the definition and scope of plagiarism and collusion are presented below:

Plagiarism is presenting somebody else’s work as your own. It includes copying information directly from the Web or books without referencing the material; submitting joint coursework as an individual effort.

Collusion is copying another student’s coursework; stealing coursework from another student and submitting it as your own work.

Suspected plagiarism or collusion will be investigated and if found to have occurred will be dealt with according to the college procedure. (For details on Plagiarism & Collusion please see the student handbook)

  1. Submission

  1. The initial submission of coursework to the tutors is compulsory in each unit of the course
  2. A student must check their assignments on ICON VLE with plagiarism software Turnitin to make sure the similarity index for their assignment stays within the College approved level. A student can check the similarity index of their assignment three times in the Draft Assignment submission point located on the home page of the ICON VLE.
  3. All Final coursework must be submitted to the Final submission point into the unit (not to the Tutor). A student would be allowed to submit only once and that is the final submission.
  4. Any computer files generated such as program code (software), graphic files that form part of the coursework must be submitted as an attachment to the assignment with all documentation.
  5. The student must attach a tutor’s comment in between the cover page and the answer sheets in the case of Resubmission.
  1. Good practice

Make backup of your work in different media (hard disk, floppy disk, memory stick etc) to avoid distress for loss or damage of your original copy

  1. Extension and Late Submission
  1. If you need an extension for a valid reason, you must request one using an extenuating circumstances request form available from the college exam office and ICON VLE. Please note that the lecturers do not have the authority to extend the coursework deadlines and therefore do not ask them to award a coursework extension. The completed form must be accompanied by evidence such as a medical certificate in the event of you being sick
  2. Late submission will be accepted and marked according to the college procedure. It is noted that late submission may result in grading from PASS.
  1. Submission deadlines

Submit to: Online to the ICON VLE only


Analyze: Break an issue or topic into smaller parts by looking in-depth at each part. Support each part with arguments and evidence for and against (Pros and cons).

Critically Evaluate/Analyse: When you critically evaluate your look at the arguments for and against an issue. You look at the strengths and weaknesses of the arguments. This could be from an article you read in a journal or from a textbook.

Discuss: When you discuss you look at both sides of a discussion. You look at both sides of the arguments.

Then you look at the reason why it is important (for) then you look at the reason why it is important (against).

Explain: When you explain you must say why it is important or not important.

Evaluate: When you evaluate you look at the arguments for and against an issue.

Describe: When you give an account or representation of in words.

Identify: When you identify you look at the most important points.

Define: State or describe the nature, scope or meaning.

Implement: Put into action/use/effect

Compare: Identify similarities and differences

Explore: To find out about

Recommend: Suggest/put forward as being appropriate, with reasons.

Department of Information Technology





USE employees;






CREATE TABLE employees (

emp_no INT NOT NULL,

birth_date DATE NOT NULL,

first_name VARCHAR(14) NOT NULL,

last_name VARCHAR(16) NOT NULL,

gender ENUM (‘M’,’F’) NOT NULL,

hire_date DATE NOT NULL,

PRIMARY KEY (emp_no)

CREATE TABLE departments (

dept_no CHAR(4) NOT NULL,

dept_name VARCHAR(40) NOT NULL,

PRIMARY KEY (dept_no),

UNIQUE KEY (dept_name)

CREATE TABLE dept_manager (

emp_no INT NOT NULL,

dept_no CHAR(4) NOT NULL,

from_date DATE NOT NULL, 7

Department of Information Technology

to_date DATE NOT NULL,


FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,

PRIMARY KEY (emp_no,dept_no)

CREATE TABLE dept_emp (

emp_no INT NOT NULL,

dept_no CHAR(4) NOT NULL,

from_date DATE NOT NULL,

to_date DATE NOT NULL,


FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,

PRIMARY KEY (emp_no,dept_no)


emp_no INT NOT NULL,


from_date DATE NOT NULL,

to_date DATE,


PRIMARY KEY (emp_no,title, from_date)

CREATE TABLE salaries (

emp_no INT NOT NULL,

salary INT NOT NULL,

from_date DATE NOT NULL,

to_date DATE NOT NULL,


PRIMARY KEY (emp_no, from_date)

INSERT INTO `departments` VALUES (‘d001′,’Marketing’),

Department of Information Technology



INSERT INTO `dept_manager` VALUES (10013,’d001′,’1985-01-01′,’1991-10-01′), (10001,’d001′,’1991-10-01′,’9999-01-01′), (10002,’d002′,’1985-01-01′,’1989-12-17′), (10008,’d002′,’1989-12-17′,’9999-01-01′), (10012,’d003′,’1985-01-01′,’1992-03-21′), (10011,’d003′,’1992-03-21′,’9999-01-01′), (10014,’d004′,’1985-01-01′,’1988-09-09′), (10003,’d004′,’1988-09-09′,’1992-08-02′);

INSERT INTO `salaries` VALUES (10001,60117,’1986-06-26′,’1987-06-26′), (10001,62102,’1987-06-26′,’1988-06-25′), (10002,66074,’1988-06-25′,’1989-06-25′), (10003,66596,’1989-06-25′,’1990-06-25′), (10004,66961,’1990-06-25′,’1991-06-25′), (10005,71046,’1991-06-25′,’1992-06-24′), (10006,74333,’1992-06-24′,’1993-06-24′), (10007,75286,’1993-06-24′,’1994-06-24′), (10008,75994,’1994-06-24′,’1995-06-24′);

Maddox Smith

Greetings for the day !
Hope that you’re well !

We want to introduce ourselves as a team of professionals who are into academic writing for the last 10+ years. We can provide assignment assistance in all subjects. Our experts can provide solutions across all the topics right from Management, HR, Marketing, Finance & Accounts, Statistics, IT, childcare, nursing, law, and general writing. We provide plagiarism free work and also send a ‘Turnitin’ report along with completed work. Our services are available at reasonable cost; we entertain amendment requests from clients without any extra charges.

Our Feature Included

Ø Every assignment includes graphical representation like pie chart, bar graph, smart art and all.
Ø Free 0% plagiarism report
Ø Expert team for technical work as well.
Ø On time delivery
Ø Multiple rework facility
Ø Huge team of expert in each subject
Ø Referencing like: Harvard, APA, MLA, Oscola, automatic referencing all are familiar to our experts.

Subject we cover: Math , finance, economics, accounts, civil engineering, mechanical engineering, IT, Computer science, electrical and electronics engineering, history, geography, political science, sociology, physiology, philosophy, biology, microbiology, biotechnology, biotechnology, B-school assignments, project report, psychology, nursing assignments, medical assignments, Tourists and travelling assignments all kinds of dissertation and so on

Best Regards:
Oz Paper Help
WhatsApp:+1 585-666-2225
1 Step 1