Qualification - Higher National Diploma in Computing

Unit Name - Database Management Systems

Unit Number - Unit 38

Assignment Title - Design, Implement and Test

Learning Outcome 1: Design a database management system using a relational model to meet client requirements

Learning Outcome 2: Develop a database management system using a suitable platform

Get Unit 38 Database Management Systems - Higher National Diploma in Computing Assignment help and Solution!! Our PhD Experts Will Guide You In The Right Way!!

For the design stage the requirements are:
• Full data dictionary for the tables needed -this must reflex your original ERD
o Attributes
o Primary keys
o Foreign keys
o Data types {must consider sizes i.e. why use an INT opposed to BYTE - optimised}
o Relationships
o Data Flow Diagram (DFD)
o Screen designs (input/output)
o SQL designs - INSERT, SELECT {cross multiple tables}, UPDATE
o Relational Algebra
o The hardware/software required

On the VLE there is a test plan - this must be fully completed. The number of tests is irrelevant as long as your application is tested for security and functionality

Build the new application

I need two screencasts with audio; firstly demonstrating your application fully working and secondly explaining how the backend works

Completed test plan with screen shot evidence

Written report reviewing your solution against your original requirements detailing quality of the database, fitness for purpose, suitability against original requirements, technology constraints, strengths and improvements

Solution:

Introduction

This system is designed for monitoring the project details from tracking system and we can easily retrieve details from different sources.To manage all projects involves in the company we have to keep a record of each work in the system for the different level of development.The terms and conditions for now day's system are totally different from previous years. They have lots of options of records the records in the system. It used in different sector like description of specifies the method to create different system lifestyle which established to cover overall lifestyle and solve all the problems in the world. We have to take care about the system. In previous years classroom oriented programs are compulsory but now we are in digital world so system are having computer, laptops and many more electric gadgets in different sector. For all this type of options we have to take cares all the options in proper way so that we have different options for the growth of system in our society and the development of organization will up to the mark.The activities in which any system is involved are as follows:

• Estimate Activities in the system for longer period of time.
• Scheduling of project
• Cost control management
• Resource allocation
• Decision making
• Quality maintenance
• Estimate

In the system we can cover all the requirement as per the growth of the system and we can easily gather all information in the following system like spread sheet, word processing or database and many more way to store information. The only movement is the fluttering in the grate. In his school days, he used to believe that the fluttering film is the symbol of someone arrival. Then he started thinking about his birth-place and the old church-tower whose bells keep ringing throughout the day. This bell ringing sound looks to him like a prophecy of future events. As he was watching the film it made him fall asleep and even in his dream, he saw some soothing things. In the next morning, he keeps thinking about the same idea but he is afraid to his teacher and tries to look into the books but his mind is absent and he keeps thinking. As the classroom doors move little bit he eyes has been lit up as he has the superstition of someone coming like his beloved sister or his childhood friends. Then he addresses his son and his cradle and how he takes gentle breathing and be calm.

Data Dictionary

TABLE

ATTRIBUTE

DATATYPE

CONSTRAINT

DESCRIPTION

Department

Department Code

INTEGER

PRIMARY KEY

Department Unique code for each department

 

Department Name

VARCHAR

 

Department Name

Employee

Employee ID

INTEGER

PRIMARY KEY

Employee Unique code for each Employee

 

Employee Name

VARCHAR

 

EmployeeName

 

Address

VARCHAR

 

EmployeeAddress

 

Phone NO

INTEGER

 

Employee Phone No

 

Email

VARCHAR

 

Employee Email ID

 

City

VARCHAR

 

Employee City

 

State

VARCHAR

 

Employee state

 

Zip code

INTEGER

 

Employee Zip code

Employee_Department

ID

INTEGER

PRIMARY KEY

Employee Department Unique code for each

 

EmployeeID

INTEGER

 

Employee Unique code

 

DEPARTMENT

INTEGER

 

Department Unique code

project_types

ID

INTEGER

PRIMARY KEY

Project Types Unique code for each

 

project_types

VARCHAR

 

Project type name

Role

Role Code

INTEGER

PRIMARY KEY

Role code  Unique code for each

 

Role Name

VARCHAR

 

Role Name for each role type

 

Employee ID

INTEGER

FOREIGN KEY

Employee Unique code for each

 

Salary

VARCHAR

 

Salary of each employee

Project

Project No

INTEGER

PRIMARY KEY

Project Unique code for each

 

Project Name

VARCHAR

 

Project name for each

 

Duration

VARCHAR

 

Duration of each project

 

Type

VARCHAR

 

Type of each project

Assignment

Assignment ID

INTEGER

PRIMARY KEY

Assigning Unique code for each

 

Project No

INTEGER

FOREIGN KEY

Project Unique code

 

Employee ID

INTEGER

FOREIGN KEY

Employee Unique code

salaries

ID

INTEGER

PRIMARY KEY

Salary Unique code

 

Employee ID

INTEGER

 

Employee Unique code for each

 

salary

DECIMAL

 

Salary details for each employee

CREATE SQL SCRIPT
CREATE TABLE IF NOT EXISTS departments (
ID int(11) NOT NULL AUTO_INCREMENT,
department_namevarchar(50) NOT NULL,
PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS employees (
ID int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
contact_numbervarchar(20) NOT NULL,
email varchar(50) NOT NULL,
project_types_idint(11) NOT NULL,
PRIMARY KEY (ID),
KEY project_types_id (project_types_id)
);


CREATE TABLE IF NOT EXISTS employee_department (
ID int(11) NOT NULL AUTO_INCREMENT,
employee_idint(11) NOT NULL,
department_idint(11) NOT NULL,
PRIMARY KEY (ID),
UNIQUE KEY unique_index (employee_id,department_id),
KEY employee_id (employee_id),
KEY department_id (department_id)
);

CREATE TABLE IF NOT EXISTS project_types (
ID int(11) NOT NULL AUTO_INCREMENT,
project_typesvarchar(50) NOT NULL,
PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS salaries (
ID int(11) NOT NULL AUTO_INCREMENT,
employee_idint(11) NOT NULL,
salary decimal(8,2) NOT NULL,
date date NOT NULL,
PRIMARY KEY (ID),
KEY employee_id (employee_id)
);

CREATE TABLE IF NOT EXISTS project (
PROJECTNO VARCHAR(50),
ProjectNamevarchar(100),
Duration INTEGER,
Types int(11),
PRIMARY KEY (PROJECTNO)
);

CREATE TABLE IF NOT EXISTS ASSIGNEMENT (
ID int(11) NOT NULL AUTO_INCREMENT,
PROJECTNO VARCHAR(50),
employee_idint(11),
PRIMARY KEY (ID)
);

Computer science Assignment Help Services By Top Experts Hire World Class Writer Now !!

CONTRAINTS
ALTER TABLE employees
ADD CONSTRAINT project_types_constraint FOREIGN KEY (project_types_id) REFERENCES project_types (ID) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE employee_department
ADD CONSTRAINT department_constraint FOREIGN KEY (department_id) REFERENCES departments (ID) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT employee_constraint FOREIGN KEY (employee_id) REFERENCES employees (ID) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE salaries
ADD CONSTRAINT salaries_ibfk_1 FOREIGN KEY (employee_id) REFERENCES employees (ID);


ALTER TABLE ASSIGNEMENT
ADD CONSTRAINT departmentassign_constraint FOREIGN KEY (PROJECTNO) REFERENCES PROJECT (PROJECTNO) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT employeeassign_constraint FOREIGN KEY (employee_id) REFERENCES employees (ID) ON DELETE CASCADE ON UPDATE CASCADE;


INSERT SCRIPT
INSERT INTO departments (ID, department_name) VALUES
(1, 'Software Development'),
(2, 'Networking'),
(3, 'Java');

INSERT INTO employees (ID, name, contact_number, email, project_types_id) VALUES
(1, 'John', '34354355', '[email protected]', 2),
(3, 'Seema ', '21249200', '[email protected]', 1);

INSERT INTO employee_department (ID, employee_id, department_id) VALUES
(1, 1, 1),
(2, 1, 2),
(5, 1, 3),
(7, 3, 1);

INSERT INTO project_types (ID, project_types) VALUES
(1, 'part_time'),
(2, 'full_time');

INSERT INTO salaries (ID, employee_id, salary, date) VALUES
(1, 1, '35000.00', '2015-07-30'),
(2, 3, '12500.50', '2015-07-30'),
(3, 1, '33000.50', '2014-12-01');

SELECT Query
QUERY 1

SELECT e.name, D.DepartmentName
FROM departments d
INNER JOIN employee_departmented ON (d.ID = ed.department_id) ;
INNER JOIN employees e ON (e.ID = ed.employee_id)

QUERY 2
SELECT e.name, P.Project_Name
FROMProject P
INNER JOIN ASSIGNMENT A ON P.projectNo=A.ProjectNo
INNER JOIN employee E ON A.employee_id=E.employee_id;

QUERY 3
SELECT e.name, D.departmentName, s.salary
FROM departments d
INNER JOIN employee_departmented ON (d.ID = ed.department_id)
INNER JOIN employees e ON (e.ID = ed.employee_id)
INNER JOIN salaries s ON (e.ID = s.employee_id)


QUERY 4

SELECT e.name, COUNT(d.department_name) AS numOfDepartments, s.salary FROM departments d
INNER JOIN employee_departmented ON (d.ID = ed.department_id)
INNER JOIN employees e ON (e.ID = ed.employee_id)
INNER JOIN salaries s ON (e.ID = s.employee_id)
INNER JOIN project_typesjt ON (jt.ID = e.project_types_id)
WHERE jt.project_types = "part_time" AND s.date = (
SELECT MAX(s2.date) FROM salaries s2
WHERE e.ID = s2.employee_id
)
GROUP BY e.name, s.salary
HAVING numOfDepartments> 0

Relational Algebra
EMP( EMPNO, NAME, DNO, JOB, MGR, SAL, COMMISSION)
DEPT( DNO, DNAME, LOC)
Q1. Find the subset of employees who are in dept. 50.
σDNO = 50(EMP)
Q2. Find the subset of employees who are in dept. 25, 47 or 53.
ΣDNO = 25 or DNO = 47 or DNO = 53(EMP)

Q3. List names and jobs of all employees.
π NAME, JOB
(EMP)

Q4. List names and jobs of employees in departments
25, 47 or 53.
π NAME, JOB
(σDNO = 25 or DNO = 47 or DNO = 53(EMP))

D_EMPS ← σDNO = 25 or DNO = 47 or DNO = 53
(EMP)
RESULT ← π NAME, JOB
(D_EMP)

The hardware/software required

Hardware required in the system:
• Desktop
• Mouse
• Printer
• Keyboard

Software required in the system:
• Window 7/10
• MySQL
• Xampp
• Ms word
• Ms Excel
• Visio

TEST PLAN

Test plan is used for checking the output of the system time to time that system is working properly or not. In the system we have to check the poll the quality of the product. In the test plan there are different steps which are mentioned below:

1) Master Test plan
2) Testing Level Specific Test Plan
3) Testing Type Specific Test Plan

• Master test Plan is high-level test plan for any project or product which can be unifies at different steps and can used for different level of testing.
• Testing Level test plan monitor all different plan like Unit Test Plan, Integration Test Plan, System Test plan and different test plans can be used at different level for different level.
• All plans for major types can be performaned test plan for different level.

RELATED COURSES & ASSIGNMENT SERVICE!!


COMMENTS(0)

LEAVE A COMMENT


Captcha

 

 

Are You Looking for Database Management Systems Assignment Help?


Our professional Will Help You To Provide Best Unit 38 Database Management Systems - Higher National Diploma in Computing Assignment Help Services!!

Learning Outcomes and Assessment Criteria

 

LearningOutcome

 

Pass

 

Merit

 

Distinction

LO2 Design a database management system using a relational model to meet client requirements

 

LO3Develop a Database management system using a suitable platform

 

P2 Produce a design for arelational database

management system to

meet client requirements

 

P3 Develop a fully

functional system which

meets client and system

requirements, using an

open source language

 

P4 Test the system for

functionality and

performance.

 

P5 Demonstrate the toolsavailable in the system tomonitor and optimisesystem performance, andexamine the audit logs.

 

P6 Demonstrate the toolsavailable in the system tomanage security andauthorisations.

M2 Analyse how the designwill optimise systemperformance

 

M3 Implement effective

features in the solution tohandle concurrency,

security, userauthorisations and data

recovery

 

M4 Assess the

effectiveness of the systemadministration andmanagement tools

available on the platformidentifying any

shortcomings of the tools.

D2 Critically evaluate

the effectiveness of

the system design and

development against

client and system

requirements

Avail the Services for its related units and courses such as:-

  • Unit 27 Transport Network Design Assignment Help
  • Unit 25 Machine Learning Assignment Help
  • Unit 16 Computing Research Project (Pearson Set) Assignment Help
  • Unit 22 Application Development Assignment Help
  • Unit 18 Discrete Maths Assignment Help
  • Unit 24 Advanced Programming for Data Analysis Assignment Help
  • Unit 20 Applied Programming and Design Principles Assignment Help
  • Unit 21 Application Program Interfaces Assignment Help
  • Unit 26 Big Data Analytics and Visualisation Assignment Help
  • Unit 17 Business Process Support Assignment Help
  • Unit 23 Risk Analysis & Systems Testing Assignment Help
  • Unit 19 Data Structures & Algorithms Assignment Help