Database Management Systems Assignment Help

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

Craft a Robust Relational Database Solution for Complex Challenges - Get Expert Assistance with Database Design and Development Assignments

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

Execute the suggested research inquiry with a tailored, well-crafted solution that aligns precisely with your needs and specifications.

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

Examine the influence of social policy, legislation, and cultural factors on health and social care practices, emphasizing the principles guiding these interactions.

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.