Entity Relationship Diagram Analysis

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

In this task I have to collect some entity, then an entity attributes and at last ERD.I think I will learn more about entity, attributes, Entity Relationship Diagram to do this task.

Description:

Entity

An Entity is an object or real thing such as a table or form. An Entity contains some information about himself. In the entity some data can be stored.

Attributes

Attributes means the information or details of an Entity. Attribute may contain various kinds of value like numeric, Characters, Integer etc. Simply attributes are the properties of entity.

Entity Relationship Diagram (ERD)

An entity-relationship diagram is a data modeling diagram and technique. ERD can create a graphical representation of the relationships between entities, the entities, and within an information system.

There are three type relationships in the Entity Relationship Diagram (ERD)

One to One

One to Many

Many to Many

The main components of Entity Relationship Diagram (ERD)

Entity

Attributes

Relationship

Cardinality

Entities:

Department

Subject Area

Staff

Program

Module

Student

Registration

Exam

Result

Coursework

Attendance

Progress

Entities with Attributes:

Department

Department_ID(PK)

Department_Name

Department_Details

Department_Leader

Subject_Area

Subject_Area_ID(PK)

Subject_Area_Leader

Subject_Area_Name

Subject_Area_Details

Department_ID(FK)

Employee

Employee_ID(PK)

Employee_Leader

Employee_Type

Employee_Name

Employee_JoiningDate

Employee_Address

Department_ID(FK)

Program

Program_ID(PK)

Program_Leader

Program_Name

Program_Type

Program_Details

Subject_Area_ID(FK)

Registration

Registration_ID(PK)

Registration_Date

Registration_Type

Registration_cost

Module

Module_ID(PK)

Module_Name

Module_Leader

Module_Details

Module_Type

Student_ID(FK)

Program_ID(FK)

Student

Student_ID(PK)

Student_Name

Student_Type

Student_Details

Student_Address

Module_ID(FK)

Registration_ID(FK)

Exam

Exam_ID(PK)

Exam_Details

Exam_Name

Exam_Date

Exam_Time

Student_ID(FK)

Result

Result_ID(PK)

Result_Name

Result_Type

Result_Details

Result_Date

Result_time

Exam_ID(FK)

Coursework

Coursework_ID(PK)

Coursework_Name

Coursework_Type

Coursework_Details

Student_ID(FK)

Attendance

Attendance_ID(PK)

Attendance_Name

Attendance_Type

Attendance_Details

Student_ID(FK)

Progress

Progress_ID(PK)

Progress_Type

Progress_Details

Entity Relationship Diagram (ERD):

Relationship summary:

In my ERD diagram some relation description are given in the below:

In the Entity Relationship Diagram I find out the relation between department and subject area is one to many, because one department may have many subject areas.

In the scenario I can see one subject area may have more programs.

In the ERD diagram the relationship between program and module is one to many. Many modules are under of a program. Module is used a foreign key of programs.

In the ERD diagram the relationship between students and modules are one to many, because one student may have more than one module. In the relationship student primary key is used as a foreign key in the module entity.

In the ERD diagram the relationship between student and exam is one to many. One student may participate more exams.

In the ERD diagram the relationship between student and coursework is one to many. One student may participate in the more coursework.

In the ERD diagram the relationship between registration and student is one to many. The registration may be registries for more students at same time.

In the ERD diagram actually I see the one to many relations. For this reason I include one to many relationships in the ERD diagram. In the real life for database one to many relationship is perfect to me.

Summary

In this task I draw an entity relationship diagram. To draw the diagram I find out some entities and attributes of entities. To do this task I also learn about entities, attributes and entity relationship diagram.

Task-02

Introduction:

It is said to normalize in the task 2. For making a database successfully Normalization must be needed. The activities of a database depend on normalization. Normalization helps to make a database defiantly. In the task I will try to normalize for a department system in the organization. The normalization occurs in some steps. In the following steps are given.

Description:

Normalization

In the organization all activities now depend on the computer system. Some corporate organization need database system for controlling all information. As a result database is most popular and essential things in the world. For making a database successfully normalization is the traditional way. Without normalization it is impossible to make a database. Normalization is the process is used to organize the data perfectly in a database. I normalize the department data for some specific purpose. The purpose of normalization is divided into two parts. Removing unnecessary data and ensuring data dependencies. If same data stay in some table together the database may victim. Often it may data in the unrelated table. To solve these problems we can be done normalization. The purposes of normalization are worthy to decrease the amount of space a database consumes and ensure that data is logically stored. The performance of database management system depends on the normalization.

Some steps are essential for normalization. The steps are given briefly in the following:

Gathering data

Gathering data is the first step of normalization. When I normalize I will collect all information in the stage. I will implement all data of this step in the next step.

Choosing a key

For doing normalization a key is essential. The key is the main data for normalization. All data depend on the key. The key must be one from gathering data.

First Normal Form (1NF)

Some roles have to follow in the steps. Repeating data in the normalization, sometime it may harmful for normalization, so it necessary to remove repeating data in the normalization. Removing repeating data is the main work in this task.

Second Normal Form (2NF)

Second normal form (2NF) is used to different and remove same data in the database. The stage also provides further information for removing same data in the database. The main activities of the stage:

The all data of first normal form must be represented in the stage.

Same data in the rows of database table are removed in the stage.

The relationship of new table is made in the stage.

Third Normal Form (3NF)

In the third normal form some activities are occurred. The following activities are given in the below:

All requirements of the second normal form are present in the stage.

The activities of the stage is removed some columns that are not dependent upon the primary key. The removing data and information are not depended on the primary key of table. For this reason these columns data and information are removed.

Optimization

Optimization is the last step of normalization. I will produce the whole normalization by producing a normalization diagram. The normalization diagram is symbol of a better database. If normalization table is clear, data base become clear.

Gathering Data

Department_ID

Department_Name

Department_Details

Department_Leader

Subject Area_ID

Subject Area_Leader

Subject Area_Name

Subject Area_Details

employee_ID

employee_Leader

employee _Type

employe _Name

employee_JoiningDate

Staff_Address

Programme_ID

Programme_Leader

Programme_Name

Programme_Type

Programme_Details

Student_ID

Registration_ID

Registration_Date

Registration_Type

Module_ID

Module_Name

Module_Leader

Module_Description

Module-Type

Student_ID

Student_Name

Student_Type

Student_Description

Student_Address

Module_ID

Registration_ID

Exam_ID

Exam_Description

Exam_Name

Exam_Date

Exam_Time

Result_ID

Result_Name

Result_Type

Result_Description

Result_Date

Result_time

Coursework_ ID

Coursework_Name

Coursework_Type

Coursework_Details

Attendance_ID

Attendance_Name

Attendance_Type

Attendance_Details

Progress_ID

Progress_Type

Progress_Details

Choosing a key:

Student_id

Converting to 1st Normal Form (Remove to repeating data)

Student (Repeating):

Department_id

Department_name

Student_id

Student_name

Student_email

Student_phone

Student_address

Study_mode

Employee_id

Employee_name

Employee_phone

Employee_email

Employee_addresses

SEX

Program_id

Program_name

Program_time

Credit_id

Total_course_marks

Total_module_marks

Total_credit

Result

Module_id

Module_name

Module_leader

Module_year

Subject Area (Non Repeating):

Subjectarea_id

Subjectarea_name

Subjectarea_Leader

Converting to 2nd Normal Form (Remove parcel key dependence)

Student

Student_id(pk)

Student name

Student email

Studentphone

Student address

Study mode

Program id(fk)

Programe name

Program leader

Credit

Credit id(pk)

Student id(fk)

Total coursework mark

Exam mark

Module mark

Total credit

Result

Module

Module id(pk

Module leader

Module name

Programe id(fk)

Programe name

Employee

Employee id(pk)

Employee name

Employee email

Employee phone

Employee address

Sex

Depatment id(fk)

Department name

Department

Department id(pk)

Department name

Program

Program id(pk)

Subject area id(fk)

Subject area name

Programe name

Program leader

Subject area

Subject area id(pk)

Subject area name

Department id(fk)

Department name

Subject area leader

Converting to 3rd Normal Form :( Remove non key dependency):

Department

Department_id(PK)

Department_name

Subject Area

Subjectarea_id(PK)

Subjectarea_name

Department_id(FK)

Subjectarea_Leader(FK)

Employee

Employee_id(PK)

Employee_name

Employee_phone

Employee_email

Employee_addresses

SEX

Department_id(FK)

Student_module

Student_id(PK)

Module_id(PK)

Program

Program_id(PK)

Subjetarea_id(FK)

Program_leader(FK)

Program_name

Program_time

Module

Module_id(PK)

Module_name

Module_leader(FK)

Module_year

Program_id(FK)

Student

Student_id(PK)

Student_name

Student_email

Student_phone

Student_address

Study_mode

Program_id(FK)

Credit

Credit_id(PK)

Student_id(FK)

Module_id(FK)

Total_course_marks

Total_module_marks

Total_credit

Result

Optimization:

Summary:

In the task I normalized for a department. I normalize for maintaining database management system. To do this task I analyzed about normalization. I think I normalized well. Clear normalization helps to make a better database management system. I learned more about normalization to do this task.

Task 3

Introduction:

The task 3 contains some topics. Database Management System (DBMS) is component of the task. I will create a database by using Database Management System (DBMS). There are some ways to make database. But I will create database in the SQL server, because it is easy and flexible to me. To make database I will make some table, each table will contain minimum 5 records.

Description:

Some steps I followed to do this task. The steps and requirements are given in the below:

Database Management System (DBMS):

To manage a database some strong roles are followed. Database management system is a kind of environment where all kinds' roles and regulation are included. A database management system (DBMS) works as a database manager. Database Management System (DBMS) is computer program or software. The software helps others computer users create and access data in a database. Anyone can access the data, where data is located it is not fact, It manages user requests so that users and other programs are free from having to understand data in a multi-user system, on storage media and, who else may also be accessing the data. Database management system checks the integrity of the database. The DBMS also manage the authentication process, only authorized user and authorized network can access the database. DBMS helps to make a secure database. Database management system's environment helps to make a good relationship in the database.

The activities for this task are following:

MY database:

Generated Script from "Generate SQL Server Scripts Wizard":

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[department]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[department](

[department_id] [nchar](10) NOT NULL,

[department_name] [nvarchar](50) NULL,

CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED

(

[department_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[employee]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[employee](

[employee_id] [nchar](10) NOT NULL,

[employee_name] [nvarchar](50) NULL,

[employee_email] [nvarchar](50) NULL,

[employee_phon] [int] NULL,

[employee_address] [nvarchar](50) NULL,

[department_id] [nchar](10) NULL,

[sex] [nvarchar](1) NULL,

CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED

(

[employee_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[subjectarea]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[subjectarea](

[subjectarea_id] [nchar](10) NOT NULL,

[subjectarea_name] [nvarchar](50) NULL,

[department_id] [nchar](10) NOT NULL,

[subjectarea_leader] [nchar](10) NOT NULL,

CONSTRAINT [PK_subjectarea] PRIMARY KEY CLUSTERED

(

[subjectarea_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[program]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[program](

[program_id] [nchar](10) NOT NULL,

[subjectarea_id] [nchar](10) NULL,

[program_name] [nvarchar](50) NULL,

[program_leader] [nchar](10) NULL,

[program_time] [nvarchar](50) NULL,

CONSTRAINT [PK_program] PRIMARY KEY CLUSTERED

(

[program_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[module]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[module](

[module_id] [nchar](10) NOT NULL,

[program_id] [nchar](10) NULL,

[module_leader] [nchar](10) NULL,

[module_name] [nvarchar](50) NULL,

[year] [nchar](10) NULL,

CONSTRAINT [PK_module] PRIMARY KEY CLUSTERED

(

[module_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[student]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[student](

[student_id] [nchar](10) NOT NULL,

[student_name] [nvarchar](50) NULL,

[student_email] [nvarchar](50) NULL,

[student_phon] [int] NULL,

[student_address] [nvarchar](100) NULL,

[program_id] [nchar](10) NULL,

[StudyMode] [nchar](10) NULL,

CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED

(

[student_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[student_module]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[student_module](

[student_id] [nchar](10) NOT NULL,

[module_id] [nchar](10) NOT NULL,

CONSTRAINT [PK_student_module] PRIMARY KEY CLUSTERED

(

[student_id] ASC,

[module_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[credit]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[credit](

[credit_id] [nchar](10) NOT NULL,

[student_id] [nchar](10) NULL,

[module_id] [nchar](10) NULL,

[total_coursework_marks] [int] NULL,

[exam_marks] [int] NULL,

[total_module_marks] [int] NULL,

[total_credits] [int] NULL,

[result] [nchar](10) NULL,

CONSTRAINT [PK_credit] PRIMARY KEY CLUSTERED

(

[credit_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_employee_department]') AND parent_object_id = OBJECT_ID(N'[dbo].[employee]'))

ALTER TABLE [dbo].[employee] WITH CHECK ADD CONSTRAINT [FK_employee_department] FOREIGN KEY([department_id])

REFERENCES [dbo].[department] ([department_id])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_subjectarea_department]') AND parent_object_id = OBJECT_ID(N'[dbo].[subjectarea]'))

ALTER TABLE [dbo].[subjectarea] WITH CHECK ADD CONSTRAINT [FK_subjectarea_department] FOREIGN KEY([department_id])

REFERENCES [dbo].[department] ([department_id])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_subjectarea_employee]') AND parent_object_id = OBJECT_ID(N'[dbo].[subjectarea]'))

ALTER TABLE [dbo].[subjectarea] WITH CHECK ADD CONSTRAINT [FK_subjectarea_employee] FOREIGN KEY([subjectarea_leader])

REFERENCES [dbo].[employee] ([employee_id])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_program_employee]') AND parent_object_id = OBJECT_ID(N'[dbo].[program]'))

ALTER TABLE [dbo].[program] WITH CHECK ADD CONSTRAINT [FK_program_employee] FOREIGN KEY([program_leader])

REFERENCES [dbo].[employee] ([employee_id])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_program_subjectarea]') AND parent_object_id = OBJECT_ID(N'[dbo].[program]'))

ALTER TABLE [dbo].[program] WITH CHECK ADD CONSTRAINT [FK_program_subjectarea] FOREIGN KEY([subjectarea_id])

REFERENCES [dbo].[subjectarea] ([subjectarea_id])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_module_employee]') AND parent_object_id = OBJECT_ID(N'[dbo].[module]'))

ALTER TABLE [dbo].[module] WITH CHECK ADD CONSTRAINT [FK_module_employee] FOREIGN KEY([module_leader])

REFERENCES [dbo].[employee] ([employee_id])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_module_program]') AND parent_object_id = OBJECT_ID(N'[dbo].[module]'))

ALTER TABLE [dbo].[module] WITH CHECK ADD CONSTRAINT [FK_module_program] FOREIGN KEY([program_id])

REFERENCES [dbo].[program] ([program_id])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_student_program]') AND parent_object_id = OBJECT_ID(N'[dbo].[student]'))

ALTER TABLE [dbo].[student] WITH CHECK ADD CONSTRAINT [FK_student_program] FOREIGN KEY([program_id])

REFERENCES [dbo].[program] ([program_id])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_student_module_module]') AND parent_object_id = OBJECT_ID(N'[dbo].[student_module]'))

ALTER TABLE [dbo].[student_module] WITH CHECK ADD CONSTRAINT [FK_student_module_module] FOREIGN KEY([module_id])

REFERENCES [dbo].[module] ([module_id])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_student_module_student]') AND parent_object_id = OBJECT_ID(N'[dbo].[student_module]'))

ALTER TABLE [dbo].[student_module] WITH CHECK ADD CONSTRAINT [FK_student_module_student] FOREIGN KEY([student_id])

REFERENCES [dbo].[student] ([student_id])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_credit_module]') AND parent_object_id = OBJECT_ID(N'[dbo].[credit]'))

ALTER TABLE [dbo].[credit] WITH CHECK ADD CONSTRAINT [FK_credit_module] FOREIGN KEY([module_id])

REFERENCES [dbo].[module] ([module_id])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_credit_student]') AND parent_object_id = OBJECT_ID(N'[dbo].[credit]'))

ALTER TABLE [dbo].[credit] WITH CHECK ADD CONSTRAINT [FK_credit_student] FOREIGN KEY([student_id])

REFERENCES [dbo].[student] ([student_id])

Summary:

I used SQL server for making database. I draw the diagram by using normalized diagram. Some tables I created to produce the diagram of database. I included all table average five records. To do the task perfectly I show all tables screen shot with records.

Task 4

Introduction

The main activities are said in the task. In the task I will quire of my database. Now in the task I will try to implement. I will verify the operation of database. I will quire of the database according the expectation of the task. I will follow the activities according the task.

Description:

In the task I have to do queries for find out some requirements these are said to do in the task. In the following these activities are given in the below:

1.

Names of all the Subject Area Leaders (SALs)

Querie:

SELECT dbo.subjectarea.subjectarea_leader, dbo.employee.employee_name, dbo.subjectarea.subjectarea_name

FROM dbo.employee INNER JOIN

dbo.subjectarea ON dbo.employee.employee_id = dbo.subjectarea.subjectarea_leader

Output:

PLs along with the name(s) of the subject area

Querie:

SELECT dbo.program.program_leader, dbo.employee.employee_id, dbo.program.program_name

FROM dbo.employee INNER JOIN

dbo.program ON dbo.employee.employee_id = dbo.program.program_leader

Output:

MLs along with the name(s) of the subject area

SELECT dbo.module.module_leader, dbo.employee.employee_name, dbo.module.module_name

FROM dbo.employee INNER JOIN

dbo.module ON dbo.employee.employee_id = dbo.module.module_leader

2. Display the following information for the "Artificial Intelligence" subject area:

a) The list of programs that belong to that subject area;

Querie:

SELECT program_name

FROM dbo.program

WHERE (subjectarea_id = 'sa-03')

Output:

b) The list of modules that belong to these programs;

Querie:

SELECT dbo.program.program_id, dbo.module.module_name

FROM dbo.program INNER JOIN

dbo.module ON dbo.program.program_id = dbo.module.program_id

WHERE (dbo.program.subjectarea_id = 'sa-03')

c) The list of students who have awarded a Pass for one of the modules

Querie:

SELECT DISTINCT C.student_id, student.student_name, C.module_id, module.module_name, C.result

FROM credit AS C INNER JOIN

module ON C.module_id = module.module_id INNER JOIN

student ON C.student_id = student.student_id INNER JOIN

program ON module.program_id = program.program_id AND student.program_id = program.program_id INNER JOIN

subjectarea ON program.subjectarea_id = subjectarea.subjectarea_id

WHERE (C.result = 'pass') AND (subjectarea.subjectarea_id = 'sa-03')

Output:

3. Display the name of all the students who belong to one program - you can choose which program - of the "Database" subject area, along with the following information:

a) Their year of study;

Querie:

SELECT DISTINCT dbo.student.student_id, dbo.student.student_name, dbo.program.program_id, dbo.program.program_name, dbo.module.year

FROM dbo.student INNER JOIN

dbo.program ON dbo.student.program_id = dbo.program.program_id INNER JOIN

dbo.module ON dbo.program.program_id = dbo.module.program_id INNER JOIN

dbo.student_module ON dbo.student.student_id = dbo.student_module.student_id AND dbo.module.module_id = dbo.student_module.module_id

WHERE (dbo.program.subjectarea_id = 'sa-01')

Output:

b) The modules they study;

Querie:

SELECT DISTINCT

dbo.student.student_id, dbo.student.student_name, dbo.module.module_name, dbo.student_module.module_id, dbo.student.program_id,

dbo.program.program_name

FROM dbo.student INNER JOIN

dbo.program ON dbo.student.program_id = dbo.program.program_id INNER JOIN

dbo.module ON dbo.program.program_id = dbo.module.program_id INNER JOIN

dbo.student_module ON dbo.student.student_id = dbo.student_module.student_id AND dbo.module.module_id = dbo.student_module.module_id

WHERE (dbo.program.subjectarea_id = 'sa-01')

Output:

c) Their exam and coursework marks for these modules

Querie:

SELECT dbo.credit.student_id, dbo.student.student_name, dbo.credit.module_id, dbo.module.module_name, dbo.credit.total_coursework_marks, dbo.credit.exam_marks, dbo.student.program_id

FROM dbo.credit INNER JOIN

dbo.module ON dbo.credit.module_id = dbo.module.module_id INNER JOIN

dbo.student ON dbo.credit.student_id = dbo.student.student_id INNER JOIN

dbo.program ON dbo.module.program_id = dbo.program.program_id AND dbo.student.program_id = dbo.program.program_id

where program.subjectarea_id='sa-01'

Output:

Summary:

Some quires are done in the task according the roles of assignment. I finished the task very carefully. As a proved I show some screen shot of quires. I also show SQL quires in the assignment.

Task 5

Introduction:

There are five tasks in the assignment. Each task is made for a specific and different topic. Such a task is task 5. In the task 5 I will try to represent a report for whole assignment. The report will contain the description of my system design for a department of an organization. In this report I will also include the details information about task one to five.

Description

In the stage I will try to give a solution for this report.

Title

Report no- Database Design and Development

Recipient- Mrs Jesmin Aktar

Author- Shanta Maria

Introduction of my assumption:

The title of the assignment is College Database Development. The college needs a computerized system for maintaining the activities of a department. To maintaining the department activities the college needs an ideal database management system. To finish these activities of this assignment I mentioned each topic for the assignment in the individually. I think I learned more things as student, to do this assignment. After finishing each task correctly I will produce a report for discover my skill on my analysis, design and thinking skill.

Overview of whole assignment:

The summary of the whole assignment, I want to produce a report. The report is given at the below:

Task 1

Entity

An Entity is a real thing such as a table or form. An Entity contains some information about himself. The information of entity can help for identification of entity.

Attributes

Attributes refers the details information of an Entity. There are many types are fixed for attribute. These values are like numeric, Characters, Integer etc. Attributes are the main property of entities.

Entity Relationship Diagram (ERD)

An entity-relationship diagram is a diagram for data modeling. Entity relationship diagram ensures the relationships between entities, the entities, and within an information system. Entity relationship diagram makes a database well understand.

There are three type relationships in the Entity Relationship Diagram (ERD)

One to One

One to Many

Many to Many

The main components of Entity Relationship Diagram (ERD)

Entity

Attributes

Relationship

Cardinality

Task 2

Normalization

Some corporate organization need database system for controlling all information. To control information of organization database must be needed. As a result database is most popular and essential things in the world. Without normalization it is impossible to make a database. Normalization is the process is used to organize the data perfectly in a database. I normalize the department data for some specific purpose. The purposes of normalization are worthy to decrease the amount of space a database consumes and ensure that data is logically stored. The performance of database management system depends on the normalization.

Some steps are essential for normalization.

In the assignment I followed some steps for normalization. The steps are given in the below:

Gathering data

Choosing a key

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Optimization

Task 3

I used SQL server to make database in this task. Making database was the role of the task. As a result I designed a diagram and a database in the task.

Task 4

The task holds information for SQL quires. According the task some quires are made to apply in the database. To do SQL quires in the task I learned more things about SQL command and other secure information of SQL server.

Terms of reference:

The range for investigated system is extensive. All the details about the system have given. The current system and the new system image are clear in the scenario. Anyone can see and compare the scenario.

The report focuses on the department of an organization. The report is producing with a details description and information of the complete system analysis and design part for the department of an organization.

Procedures and methods:

In my assignment I used some techniques. I have investigated all the procedure in many ways. In the assignment I followed the operation and practically system of college and others managing activities. The main procedure and method of the assignment is to settle the current system problems and also to deliver an ideal database system of the academy theater for maintaining the department process.

In this assignment I used entity relationship diagram and other requirements and procedures.

Recommendation:

According the users requirements and after investigation the system, I could find out some problems and some other problem in the college development diagram. The maintaining system of the college development diagram was very difficult to manage. To solve the problems I provide the new IT system. I will try and produce the very easy way to manage the college development diagram for maintaining process or systems in the new database system of College Database Development.

Requirement hardware: To establish the process some hardware and software are very essential. The name or hardware requirements are given in the following:

PC (Include all hardware)

Network Connection

SQL server

Windows XP

.NET framework

Future plan for my database:

In the assignment I produced a database management system for a college department. There are many departments of the college. I will include more information about more departments in the database at future. To implement my database I will use latest tools. I will try to find how way I can implement the database.

Appendix:

Entity Relationship Diagram (ERD):

Optimization diagram:

Database

Summary:

There are five tasks in the assignment. The assignment contains some topics of database. As a student any one can learn about database management system from the subject. The task five contains information of an assumption. In the task I tried to provide an ideal assumption about the whole assignment.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.