Definition For Entity Relationship Data Model Information Technology Essay

Identify the Entity Relationship Data (ERD) Model of the database system supporting the administration of the department at the local college.

Before going to identify the entity for the model, let us take a look to understand the definition of the Entity Relationship Data Model.

Definition for Entity Relationship Data Model

Entity-relationship model (ERM) is the data in an abstract and conceptual representation. The relationship between the individual methods of shaping is to create a database that is used leading to the concept of the type or system block diagram of the semantic data model, often a relational database and its requirements of top-down fashion. This process creation's chart calls entity-relationship diagram, ER diagram, or ERDs.

The necessary conditions for the analysis phase in period, the entity-relationship model in the database used in the design of information systems during the first phase to clarify the types of information required is stored. All ontology specific interests through a range of data modeling techniques are described. If you are designing information systems based on the database, then the conceptual data model has been assigned to a subsequent logical data model, in the physical design during the course of the contrary is mapped to a physical model.

Data model is the application on behalf of the scope of data requirements with an independent way to implement the process. Formalism defines the data model used to represent a set of construction law. Product Data shaping is a logical data model.

Definition for Entity

The entity is a distinct, separate existence of the thing, though it need not be of material existence. In particular, the abstract and the legal fiction are generally considered an individual. In general, do not speculate the entity is to give life. Entity is used to display communications and internal processing files and the order processing system, development model comparison.

Entity can be viewed as a subset of the collection contains. In philosophy, this collection is considered an abstract object.

According to the scenario of the "Database system supporting the administration" of a local college, it is identified that there are Seven core entities, namely~

1. Subject Area

2. Undergraduate programme

3. Module

4. Module Enrolment

5. Module Enrolment Exam and Coursework

6. Student Information

7. Staff Information

Below is the explanation of the content of each entity as identified above~

Subject Area Information

Entity :

The Subject Area is an entity of the Computing Department of local college.

Each Subject Area offers five undergraduate programmes lasting three years.

Subject Area is recorded with Subject ID, Name, Year and Subject Area Leader.

Relationship:

From Fig.1.1, the relationship of Subject Area to Undergraduate Programme is one-to-many (1:M).

Subject Area

Undergraduate Programme

Fig. 1.1

Undergraduate Programme

Entity :

The Undergraduate Programme is an entity of the Computing Department.

Each Undergraduate Programme offers four modules per years.

Undergraduate Programme is recorded with ID, Name, Leader and Year.

Relationship:

From Fig 1.2, the relationship of Undergraduate Programme to Module is one-to many (1:M).

Module

Undergraduate Programme

Fig. 1.2

Module

Entity :

Module is an entity of the Computing Department.

Each module has its own exam and coursework weightings

Students can attend full time or part time mode of modules studied per year

Module is recorded with ID, Name, Leader, Credit and Year.

Relationship:

From Fig 1.3, the relationship of Modules to Modules Enrolment is one-to-many (1 : M) .

Module Enrolment

Module

Fig. 1.3

Module Enrolment

Entity :

Module Enrolment is an entity of the Computing Department

Each Module Enrolment may hold more one student

Each module has its own exam and coursework weightings

Each module Enrolment has its result of exam and coursework weightings

Relationship:

From Fig.1.4, the relationship of Module Enrolment to Student Information one-to-many (1 : M) .

From Fig.1.5, the relationship of Module Enrolment to Modules Coursework Result one-to-many (1 : M) .

Student Information

Module Enrolment

Fig. 1.4

Module Exam and Coursework Result

Module Enrolment

Fig. 1.5

Student Information

Entity :

Student Information is an entity of the Computing Department,

Each Student can attend in difference of modules studied per year.

Relationship:

From Fig. 1.6, the relationship of Module Enrolment to Student Information one-to-many (1 : M) .

Student Information

Module Enrolment

Fig. 1.6

Module Exam and Coursework

Entity :

Module Exam and Coursework is an entity of the Computing Department,

Each Enrolment Exam and Coursework has its own exam and coursework weightings and results.

Relationship:

From Fig. 1.7, the relationship of Module Enrolment to Module Exam and Coursework is one-to-many (1 : M) .

Module Exam and Coursework

Module Enrolment

Fig. 1.7

Complete Entity Relationship Diagram for Computing Department

(Ovals represent Entities, and lines represent relationships between the boxes)

Module Exam and Coursework

Mark

Subject

Area

Staff Information

Module

Enrolment

Module

Undergraduate

Programme

Student

Information

Fig. 1.8

Basic concept of Entity Relationship

Below is some basic concept of which is one-to-many and many-to-many Entity Relationship~

One-to-many (1:M) relationships are often used when managing databases. A one-to-many relationship occurs when one entity is related to many occurrences in another entity. For example, one teacher has many students.

Many-to-many (M:N) relationships refers to a relationship between two entities A and B in which A may contain a parent row for which there are many children in B and vice versa. Because most DBMSs only support one-to-many relationships, it is necessary to implement such relationships physically via a third junction table, say, AB with two one-to-many relationships A -> AB and B -> AB. In this case the logical primary key for AB is formed from the two foreign keys.

Task 2

Definition for Primary key and Foreign key

Primary key

A table where primary key is the unique identifier field of a record field or a combination, so individual records can be identified without confusion.

Foreign Key

Foreign key (sometimes called a reference to the key) is the key to be used to connect two tables together. Typically, you take the primary key from a table and insert it into the field it has become another foreign key table (which in the original table to maintain a primary key).

Indicating the Primary and Foreign key for each resulting table

Producing the Table of the Primary Key (PK) and Foreign Key (FK) of the Entities:

Below is the resulting table of the entities identified in task 1, it is clearly indicating the primary and foreign keys:

No.

Table Name

Primary Key (PK)

(Field Name)

Foreign Key (FK)

(Field Name)

Subject Area

Subject_Area_ID

(SA_ID)

Subject_Area_Leader

(SA_STF_ID)

Undergraduate Programme

Undergraduate_Programme_ID

(UP_ID)

Subject_Area

(UP_SA_ID)

Undergraduate_Programme_Leader_ID

(UP_STF_ID)

Module

Module_ID

(MD_ID)

Undergraduate_Programme_ID

(MD_PI_ID)

Module_Leader_ID

(MD_STF_ID)

Module_Enrolment

Module_Enrolment_ID

(ME_ID)

Module_ID

(ME_MD_ID)

Module_Student_ID

(ME_STD_ID)

Module_Exam_and_Coursework

Modeule_Exam_and_Coursework_ID

MEC_ID

Module_Enrolment

(MEC_ME_ID)

Student _Information

Student_ID

STD_ID

Staff_Information

Staff_ID

STF_ID

Normalization

Normalization is the process of efficient organization of data in the database.

The normalization process has two goals: to eradicate duplication of data and ensure data dependency makes sense.

Both are worthy goals, because they reduce the amount of space a database consumes and ensure that the data is logically stored.

Stage of Normalization

1. Collection data set.

2. Transform un-normalized data integration table to first normal form (1NF)

First normal form (1NF) set up an organization's database, the very basic rules:

Eliminate duplication of a table from the same column.

Create a separate table for each group to identify the relevant data and a column with a unique set for each row or column (primary key).

3. Transform first normal form table to the second normal form (2NF)

Second Normal Form (2NF) further addresses the elimination of duplicated data concepts:

Meet all the requirements of first normal form.

Cancellation in separate table where applicable to multi-line table and placed a subset of their data.

Through uses the foreign keys to create between these new tables and their predecessors' relationship.

4. Transform second normal form to (3NF)

Third Normal Form (3NF) to go a step further:

Meet all the requirements of the second paradigm.

Removal does not depend on the primary key column.

5. Conversion table on the third normal form Boyce-Codd normal form (BCNF)

Finally, the fourth normal form (4NF) has one additional requirement:

Meet all the requirements of the third normal form.

If it does not multi-valued dependency, contact in 4NF.

Remember, these normalization guidelines are cumulative. For the database in 2NF, it must first fulfill all the criteria of 1NF database.

Unorganized data

Field Name (Name)

SA_Name (Subject Area Name)

SA_Leader (Subject Area Leader)

Std_First_Name (Student First Name)

Std_Last_Name (Student Last Name)

Std_Nationality (Student Nationality)

Std_Address (Student Address)

Std_Region (Student Region)

Std_City (Student City)

Std_Country (Student Country)

Std_Tel_No (Student Tel No)

Std_Sex (Student Sex)

Std_Remark (Student Remark)

Stf_First_Name (Staff First Name)

Stf_Last_Name (Staff Last Name)

Stf_Nationality (Staff Nationality)

Stf_Address (Staff Address)

Stf_Region (Staff Region)

Stf_City (Staff City)

Stf_Country (Staff Country)

Stf_Tel_No (Staff Tel No)

Stf_Sex (Staff Sex)

Stf_Remark (Staff Remark)

SA_Name (Subject Area Name)

SA_Leader (Subject Area Leader)

UP_Name (Undergraduate Programme Name)

UP_Leader (Undergraduate Programme Leader)

UP_Year (Undergraduate Programme Year)

MD_Name (Module Name)

MD_Leader (Module Leader)

Repeating fields

MD_Credits (Module Credits)

MD_Mode (Module Mode)

MD_Year (Module Year)

MD_Exam(Module Exam)

MD_Exam_Weigh (Module Exam Weigh)

MD_Coursework (Module Coursework)

MD_Lecture_No (Number of Module Lecture)

MD_Seminar_No (Number of Module Seminar)

ME_STD_Name (Module Enrolment Student Name)

ME_ATT_Lecture (Attendance in Lecture)

ME_ATT_Seminar (Attendance in Seminar)

ME_ATT_Lab (Attendance in Laboratory)

ME_Exam_Result (Exam Result)

ME_Coursework_Result (Coursework Result)

ME_Pass (Module Pass)

Fig 2.1

Fist Normal Form

Unorganized data

ïƒ

First Normal Form

Field Name

Table Name / Field Name

SA_Name

Subject Area

SA_Leader

Subject Area ID

Subject Name

Subject Leader

Student Information

Std_First_Name

Std_ID

Std_Last_Name

Std_First_Name

Std_Nationality

Std_Last_Name

Std_Address

Std_Nationality

Std_Region

Std_Address

Std_City

Std_Region

Std_Country

Std_City

Std_Tel_No

Std_Country

Std_Sex

Std_Tel_No

Std_Remark

Std_Sex

Std_Remark

STAFF

Stf_First_Name

Stf_ID

Stf_Last_Name

Stf_First_Name

Stf_Nationality

Stf_Last_Name

Stf_Address

Stf_Nationality

Stf_Region

Stf_Address

Stf_City

Stf_Region

Stf_Country

Stf_City

Stf_Tel_No

Stf_Country

Stf_Sex

Stf_Tel_No

Stf_Remark

Stf_Sex

Stf_Remark

Undergraduate Programmes

UP_Name

UP_ID

UP_Leader

UP_Name

UP_Year

UP_Leader

UP_Year

Module

MD_Name

MD_ID

MD_Leader

MD_Name

MD_Credits

MD_Leader

MD_Mode

MD_Credits

MD_Year

MD_Mode

MD_Exam

MD_Year

MD_Exam_Weigh

MD_Exam

MD_Coursework

MD_Exam_Weigh

MD_Lecture_No

MD_Coursework

MD_Seminar_No

MD_Lecture_No

MD_Seminar_No

Module Enrolment

ME_STD_Name

ME_ID

ME_ATT_Lecture

ME_STD_Name

ME_ATT_Seminar

ME_ATT_Lecture

ME_ATT_Lab

ME_ATT_Seminar

ME_Exam_Result

ME_ATT_Lab

ME_Coursework_Result

ME_Exam_Result

ME_Pass

ME_Coursework_Result

ME_Pass

Fig 2.2

Second Normal Form

First Normal Form

ïƒ

Second Normal Form

Subject Area

Subject Area ID

Subject Area ID (PK)

Subject Name

Subject Leader

Subject Leader (FK)

STAFF

Stf_ID

Stf_ID (PK)

Stf_First_Name

Stf_Last_Name

Stf_Nationality

Stf_Address

Stf_Region

Stf_City

Stf_Country

Stf_Tel_No

Stf_Sex

Stf_Remark

Undergraduate Programmes

UP_ID

UP_Name

UP_SA_ID

UP_Leader

UP_Name

UP_Year

UP_Leader

UP_Year

Module

MD_ID

MD_ID(PK)

MD_Name

MD_UP_ID

MD_Leader

MD_Name

MD_Credits

MD_Leader

MD_Mode

MD_Credits

MD_Year

MD_Mode

MD_Exam

MD_Year

MD_Exam_Weigh

MD_Exam

MD_Coursework

MD_Exam_Weigh

MD_Lecture_No

MD_Coursework

MD_Seminar_No

MD_Lecture_No

MD_Seminar_No

Module Enrolment

ME_ID

ME_ID (PK)

ME_STD_Name

ME_MD_ID (FK)

ME_ATT_Lecture

ME_STD_ID (FK)

ME_ATT_Seminar

ME_ATT_Lecture

ME_ATT_Lab

ME_ATT_Seminar

ME_Exam_Result

ME_ATT_Lab

ME_Coursework_Result

ME_Exam_Result

ME_Pass

ME_Coursework_Result

ME_Pass

Student Information

Std_ID

Std_ID (PK)

Std_First_Name

Std_Last_Name

Std_Nationality

Std_Address

Std_Region

Std_City

Std_Country

Std_Tel_No

Std_Sex

Std_Remark

Fig. 2.3

Third Normal Form

Second Normal Form

ïƒ

Third Normal Form

Subject Area

Subject Area ID (PK)

Subject Name

Subject Leader (FK)

STAFF

Stf_ID (PK)

Stf_First_Name

Stf_Last_Name

Stf_Nationality

Stf_Address

Stf_Region

Stf_City

Stf_Country

Stf_Tel_No

Stf_Sex

Stf_Remark

Undergraduate Programmes

UP_ID

UP_SA_ID

UP_Name

UP_Leader

UP_Year

Module

MD_ID(PK)

MD_UP_ID

MD_Name

MD_Leader

MD_Credits

MD_Mode

MD_Year

MD_Exam

MD_Exam_Weigh

MD_Coursework

MD_Lecture_No

MD_Seminar_No

Module Enrolment

ME_ID (PK)

ME_MD_ID (FK)

ME_STD_ID (FK)

ME_ATT_Lecture

ME_ATT_Seminar

ME_ATT_Lab

ME_Exam_Result

ME_Pass

Module Exam

ME_Coursework_Result

MEX_ID

ME_Pass

MEX_MD_ID

MEX_Mark

Student Information

MEX_Coursework_Mark

Std_ID (PK)

Std_First_Name

Std_Last_Name

Std_Nationality

Std_Address

Std_Region

Std_City

Std_Country

Std_Tel_No

Std_Sex

Std_Remark

Fig. 4.4

BCNF Form

Third Normal Form

ïƒ

BCNF

Module Exam and Coursework

Module Exam

MEX_ID

MEX_MD_ID

MEX_Mark

MEX_Coursework_Mark

Module Coursework

MCW_ID

MCW_MD_ID

MCW_Coursework_Mark

Task 3

Using a Data Base Management System (DBMS) to product the normalized tables and populate the test data.

What is Data base Management System (DBMS)

Database management system (DBMS) is to control the creation, maintenance and use of a computer database as a platform or organization and its end users a set of computer programs. It is in the database administrators (DBAs) and other experts in hand to allow organizational placement organization-wide database development control. DBMS is to help gather information on the use of joint is called the record of the database and file system software package wrap. It allows different user applications to easily access the same database. DBMSs may be used in a variety of database model in either one, such as network model or relational model. In large systems, DBMS is constructed in a way to allow users and other software to store and retrieve data. Rather than having to extract information for the user to write a computer program can be asked in the query language of simple questions. Therefore, many DBMS packages provide fourth-generation programming language (4GLs), and other application development features. It helps to specify the logical database organization and access to and use of information within the database. It provides facilities for data entry access control to enforce data integrity, processing under the control of concurrency, restore the database

Overview of DBMS

DBMS is a database to control data organization, storage, management and retrieval of a set of software programs. DBMSs based on their structure or the type of information is classified. DBMS has to accept requests from the application program's data and operating instructions the transfer of appropriate data. Must be based on one or more applicable in accordance with the format of the agreement to submit and receive question and answer. When used DBMS, you can more easily change the information system, when the organization's information requirements change. New categories of data can be increased to the database without disruption to existing systems.

DBMS Examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro. Because there are many available database management systems, in order to have them in a way it is important to be able to communicate with each other. Under this circumstance, most database software is allowed to communicate with database integration with other databases to allow an open database connectivity (ODBC) drivers. For example, a common SQL statements such as select and insert the syntax from the private program has been translated into other databases may be aware of the syntax.

What is Microsoft Access

Microsoft Access is with a graphical user interface and software development tools with the relationship between Microsoft Jet database engines, Microsoft's a relational database management system. It is the application of Microsoft Office suite, including the office of the members and the professional and higher versions, and is sold separately.

Pass into the jet under the Access database engine of its own format for storing data. It may pass directly into the database, in other, Excel, text, XML, or all ODBC data subject to data stored in the container is also imported or connection, including Microsoft SQL Server, Oracle, MySQL and PostgreSQL. Software developers and data architects can use it to develop application software and the non-programmer "power users" can use it to build simple applications. Other office applications, like Visual Basic can be that the visit, which means it is the successor to pass into the network, may lead to the database and the front end of the VBA programming, including many rich possibilities available to the user.

What is Database Management system(DBMS) of my choice?

I choose Microsoft Office Access for task 3, set-up eight normalized table and entry more five test record for each table.

Produced the tables and test records by Microsoft Access database management system:

Personal Information Table and records:-

SA_ID

SA_NAME

SA_LEADER

DATABASES

INFORMATION SYSTEMS

ARTIFICIAL INTTELLIGENCE

ENGINEERING AND TECHNOLOGY

ENVIRONMENT

* Screen for Subject Area table and records

Undergraduate Programme Table and records:-

UP_ID

UP_NAME

UP_SA_ID

UP_LEADER

UP_YEAR

HISTORY

2009/9/1

LITERATURE

2009/9/1

PHILOSOPHY AND PSYCHOLOGICAL STUDIES

2010/3/1

ENGLISH LANGUAGE

2010/3/1

MUSIC

2010/9/1

* Screen for Undergraduate Programme table and records

Module Table and records:-

MD_ID

MD_UP_ID

MD_NAME

MD_LEADER

MD_CREDITS

MD_MODE

MD_YEAR

MD_EXAM

MA ENGLISH PART 1

Part Time

1/9/2009

100

MA ENGLISH PART 2

Part Time

1/3/2010

100

MA HISTORY PART 1

Full Time

1/9/2009

100

MA HISTORY PART 2

Full Time

1/3/2010

100

THE MUSIC DISSERTATION

Part Time

1/9/2010

100

MD_EXAM

_WEIGH

MD_

COURSEWORK

MD_COURSEWORK

_WEIGH

MD_LECTURE

_NO

MD_SEMINAR

_NO

100

* Screen for Post table and records

Module Enrolment Table and records:-

_ID

ME_MD

_ID

ME_STD

_ID

ME_ATT

_LECTURE

ME_ATT

_SEMINAR

ME_ATT

_LAB

ME_EXAM

_RESULT

ME_COURSEWORD

_RESULT

ME_

PASS

Yes

* Screen for Module Enrolment table and records

Module Enrolment Exam and Coursework table and records:-

STD_ID

STD_NAME

STD_SEX

STD_BIRTHDAY

STD_HKID

STD_ADDRESS

STD_TEL_NO

STD_REG_DATE

PETER CHAN

1/4/1985

A1234567

NORTH POINT

91110000

1/9/2009

JOHNSON HO

8/6/1987

B1234567

SHATIN

96081133

1/9/2009

MAY CHAN

1/8/1988

C1234567

TAIPO

94701000

1/9/2009

IRENE CHEUNG

1/12/1986

B3102854

KOWLOON BAY

96361398

1/9/2009

TOMMY WONG

7/3/1989

C3168941

MONGKOK

60314488

2/9/2009

* Screen for Module Enrolment Exam and Coursework table and records

Task 4

Define SQL

Representative structure query language SQL. SQL is used to contact with the database. According to ANSI (American National Standards Institute), this is relational database management system standard language. SQL statements are used to perform tasks such as update data on the database, or retrieve data from the database. Some of the common uses of SQL relational database management systems are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres and so on. Although most database systems use SQL, most also have their system is usually only used in their own additional proprietary extensions. However, standard SQL commands such as "Select" "Insert" "Update" "Delete" "Create" and "Drop" can use to complete a needs to be done with a database of almost everything. This guide will provide you with the instructions in each of these commands in the basic one and allows you to put them to practice using the SQL interpreter.

What Can SQL do?

1. SQL can create new databases

2. SQL can create new tables in a database

3. SQL can create stored procedures in a database

4. SQL can insert records in a database

5. SQL can delete records from a database

6. SQL can update records in a database

7. SQL can execute queries against a database

8. SQL can retrieve data from a database

9. SQL can create views in a database

10. SQL can set permissions on tables, procedures, and views

What is RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in the RDBMS tables in that database objects are stored.

Table is related to the collection of data entry, and it includes columns and rows.

1. Provide printouts of SQL 1

Display the name of all the SALs, PLs and MLs along with the name(s) of the subject area, programme(s) and/or modules(s) they lead.

Display the name of all the SALs, PLs and MLs of subject area

SELECT [SUBJECT AREA].SA_ID, [SUBJECT AREA].SA_NAME, [UNDERGRADUATE PROGRAMMES].UP_NAME, MODULE.MD_NAME

FROM ([SUBJECT AREA] INNER JOIN [UNDERGRADUATE PROGRAMMES] ON [SUBJECT AREA].SA_ID = [UNDERGRADUATE PROGRAMMES].UP_SA_ID) INNER JOIN [MODULE] ON [UNDERGRADUATE PROGRAMMES].UP_ID = MODULE.MD_UP_ID;

Display the name of all the SALs of subject area

SELECT [SUBJECT AREA].SA_NAME, [STAFF INFORMATION].STF_NAME

FROM [STAFF INFORMATION] INNER JOIN [SUBJECT AREA] ON [STAFF INFORMATION].STF_ID = [SUBJECT AREA].SA_LEADER;

2. Provide printouts of SQL 2

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

a. the list of programmes that belong to that subject area;

SELECT [SUBJECT AREA].SA_NAME, [UNDERGRADUATE PROGRAMMES].UP_NAME

FROM [SUBJECT AREA] INNER JOIN [UNDERGRADUATE PROGRAMMES] ON [SUBJECT AREA].SA_ID = [UNDERGRADUATE PROGRAMMES].UP_SA_ID

WHERE ((([SUBJECT AREA].SA_NAME)="ARTIFICIAL INTELLIGENCE"));

b. the list of modules that belong to these programmes;

SELECT [SUBJECT AREA].SA_NAME, MODULE.MD_NAME

FROM ([SUBJECT AREA] INNER JOIN [UNDERGRADUATE PROGRAMMES] ON [SUBJECT AREA].SA_ID = [UNDERGRADUATE PROGRAMMES].UP_SA_ID) INNER JOIN [MODULE] ON [UNDERGRADUATE PROGRAMMES].UP_ID = MODULE.MD_UP_ID

WHERE ((([SUBJECT AREA].SA_NAME)="ARTIFICIAL INTELLIGENCE"));

c. the list of students who have awarded a pass for one of the modules.

SELECT [SUBJECT AREA].SA_NAME, [UNDERGRADUATE PROGRAMMES].UP_NAME, MODULE.MD_NAME, [MODULE ENROLMENT].ME_PASS, [STUDENT INFORMATION].STD_NAME

FROM ([SUBJECT AREA] INNER JOIN [UNDERGRADUATE PROGRAMMES] ON [SUBJECT AREA].SA_ID = [UNDERGRADUATE PROGRAMMES].UP_SA_ID) INNER JOIN ([STUDENT INFORMATION] INNER JOIN ([MODULE] INNER JOIN [MODULE ENROLMENT] ON MODULE.MD_ID = [MODULE ENROLMENT].ME_MD_ID) ON [STUDENT INFORMATION].STD_ID = [MODULE ENROLMENT].ME_STD_ID) ON [UNDERGRADUATE PROGRAMMES].UP_ID = MODULE.MD_UP_ID

WHERE ((([SUBJECT AREA].SA_NAME)="Artificial Intelligence") AND (([MODULE ENROLMENT].ME_PASS)=Yes));

3. Provide printouts of SQL 3

Display the name of all the students who beling to one programme.

a. their year of study

MODULE ENROLMENT].ME_YEAR

WHERE ((([SUBJECT AREA].SA_NAME)="DATABASES"));

b. the modules they study

SELECT [SUBJECT AREA].SA_NAME, MODULE.MD_NAME, [STUDENT INFORMATION].STD_NAME, [MODULE ENROLMENT].ME_YEAR

WHERE ((([SUBJECT AREA].SA_NAME)="DATABASES"));

c. their exam and coursework marks for these modules.

SELECT [SUBJECT AREA].SA_NAME, MODULE.MD_NAME, [STUDENT INFORMATION].STD_NAME, [MODULE ENROLMENT].ME_EXAM_RESULT, [MODULE ENROLMENT].ME_COURSEWORD_RESULT, [MODULE ENROLMENT].ME_PASS, [MODULE ENROLMENT].ME_YEAR

WHERE ((([SUBJECT AREA].SA_NAME)="DATABASES"));

Task 5

Executive Summary

Explain the assumption when analysis, designing and implementation the above database.

The following is the essential features when making assumption for the development of database system

1. Define the key stage of the data base development process

2. Describe the key feature of the conceptual modeling

3. Outline the key feature of logical modeling

4. Describe the key feature of physical modeling

5. Relate the key elements of the data base development toolkit

In addition, there are four main functions of DBMS should also be taken into consideration as well when undergoing design, please refer to the diagram.

Data Definition - Defining new data structures for a database, removing data structures from the database, modify the structure of existing data.

Data Maintenance - Inserting new data into existing data structures, updating data in existing data structure, deleting data from data structure.

Data Retrieval - Querying existing data by users and extracting data for use by application programs.

Data Control - Creating and monitoring use of the database, restricting user access to data in the database and monitoring the performance of databases.

According to the scenario, there are many entities exist in the "Computing Department" of Local College. Each entity has its own status and contents as well as the relationship with each others.

1. To identity all valid entities and all attributes of each entity

2. To identify all relationship among entities

3. Draw an Entity Relationship diagram

4. Normalize all entities from Unorganized data into BCNF (3NF)

5. Create file for Microsoft Access

6. Create five tables at Microsoft Access

7. Define correct primary keys, foreign keys and data types for each table

8. Prepare the over five test records needed for each table

9. To use the common standard interface to most contemporary DBMS: SQL

10. To form a table using SQL with the following components~

The name of the table

The name of each of the column field in the table

The data type of each column field

The maximum length of each column field

11. To assign the primary key and at least one foreign key for relationship connection

Make sure that each SQL execution will return correct result record

Database implementation involves using the strategies developed as output from physical database design to make decision as to constructing the database system using the mechanisms or features of some DBMS.

Discuss Task 1

Produce an Entity Relationship Data Model

1. Understand the scenario of Database system supporting the administration of the Department of local college

2. To Identity entities, relationships, and attributes for Database system of Local College

3. Draw an Entity Relationship Data Model that describes the content and structure of data held by the Database system.

Definition for Entities, Relationship and Attributes

An entity may be defined as a thing which is recognized as being capable of an independent existence and which can be uniquely identified. An entity is an abstraction from the complexities of some domain. When we speak of an entity we normally speak of some aspect of the real world which can be distinguished from other aspects of the real world.

An entity may be a physical object such as a house or a car, an event such as a house sale or a car service, or a concept such as a Std transaction or order. Although the term entity is the one most commonly used, following Chen we should really distinguish between an entity and an entity-type. An entity-type is a category. An entity, strictly speaking, is an instance of a given entity-type. There are usually many instances of an entity-type. Because the term entity-type is somewhat cumbersome, most people tend to use the term entity as a synonym for this term.

Produce the resulting tables clearly indicating the primary and foreign keys.

No.

Table Name

Primary Key (PK)

(Field Name)

Foreign Key (FK)

(Field Name)

Subject Area

Subject_Area_ID

(SA_ID)

Subject_Area_Leader

(SA_STF_ID)

Undergraduate Programme

Undergraduate_Programme_ID

(UP_ID)

Subject_Area

(UP_SA_ID)

Undergraduate_Programme_Leader_ID

(UP_STF_ID)

Module

Module_ID

(MD_ID)

Undergraduate_Programme_ID

(MD_PI_ID)

Module_Leader_ID

(MD_STF_ID)

Module_Enrolment

Module_Enrolment_ID

(ME_ID)

Module_ID

(ME_MD_ID)

Module_Student_ID

(ME_STD_ID)

Module_Exam_and_Coursework

Modeule_Exam_and_Coursework_ID

MEC_ID

Module_Enrolment

(MEC_ME_ID)

Student _Information

Student_ID

STD_ID

Staff_Information

Staff_ID

STF_ID

Discuss Task 2

Normalize there table to ensure that are all in BCNF (3NF)

Main point is normalization and steps from unorganized data to third normal

1. Collect the data-set of data-items.

2. Transform the unorganized data-set into tables in first normal form.

3. Transform first normal form table to second normal form.

4. Transform second normal form table to third normal form.

5. Transform third normal form to Boyce-Cod normal form.

Discuss Task 3

Using a Data Base Management System (DBMS)

We used Microsoft Access 2002 database Management system to set-up the normalized tables and populate them with some test data.

Create five tables (Personal Information table, Qualification table, Post table, Role table and Department table)

Define relationships between tables create a relationship between tables

Prepared and entry over five test records for each table

Check relationship between tables and data is correct

Discuss Task 4

As an alternative, it is recommended to adopt Structured Query Language (SQL) to manage the valuable data of the college. SQL is a widely used powerful relational database management systems (RDBMS), which manages data as a collection of tables and all relationship are represented by common values in related tables.

Benefits of a Standardized Relational Language~

1. Reduced training costs

2. Productivity

3. Application portability

4. Application longevity

5. Reduced dependence on a single vendor

6. Cross-system communication

SQL Environment

1. Catalog - a set of schemas that constitute the description of a database

2. Schema - The structure that contains descriptions of objects created by a user (base tables, views, constraints)

3. Data Definition Language (DDL) - Commands that define a database, including creating, dropping and altering tables and establishing constraints

4. Data Manipulation Language (DML) - Commands (C - Create, R - Read, U - Update and D - Delete, i.e. CRUD) that maintain and query a database

5. Data Control Language (DCL) - Commands that control a database, including administering privileges and committing data and parts of the database sublanguage designed for use by the 6. Database administrator (DBA)

7. Data integrity Language (DIL) - Commands that used to specify integrity constraints

Definition For Entity Relationship Data Model Information Technology Essay

You may also find these documents helpful