UNIT 18 P2 - Design a relational database for a specified user need - ALL ASSIGNMENTS HERE. PASS, MERITS AND DISTINCTIONS

UNIT 18 P2 - Design a relational database for a specified user need

>>Download the database for UNIT 18  here<<

>Please click here to download UNIT 18 P2 in a word document for a better view on how P2 is. To view diagrams and images download the work in a word document<

Task1a

The table shown is about data bases. What this means is that the information would be structured in a format to be used for many purposes.

Student:

Fieldname	Data type	Field size	Format	Decimals
Studentno	Text	6	-	-
Fname	Text	11	-	-
Sname	Text	9	­-	-
Street	Text	21	-	-
Area	Text	13	-	-
Gender	Text	6	-	-
DBirth	Date	-	Medium Date	-

Room

Fieldname	Date type	Field size	Format	Decimals
Room no	text	3	-	-
Phone	text	4	-	-

Tutor

Fieldname	Data type	Field size	Format	Decimals
Tutorno	Text	6	-	-
TFname	Text	7	-	-
TSname	Text	7	­-	-
Roomno	Text	3	-	-

Course

Fieldname	Data type	Field size	Format	Decimals
Courseno	Text	6	-	-
Coursetitle	Text	25	-	-
Courseyear	Text	3	­-	-
TutorNo	Text	6	-	-

Enrol

Fieldname	Data type	Field size	Format	Decimals
Studentno	Text	6	-	-
Courseno	Text	6	-	-
Fees	Number	Double	­Fixed	2

ERM

 

The ERM diagram is showing the way students would enter the course by relying on a number of steps which are linked together. The student is related to enrolment because the students enrol first before the person is able to be on the course. The student may enrol on a number of courses but is only able to have one tutor. The tutor is able t have more than one student and rooms.

Data Dictionary

College	New students	Graduating students	Change
Table no:	1
Table Name	Student
Purpose	To store student data.
Field no	1.1	Field no	1.5
Field name	StudentNo	Field name	Area
Primary Key	Yes	Primary Key	No
Data type	Text	Data type	Text
Field size	6	Field size	13
Format	-	Format	-
Decimal	-	Decimal	-
Input mask:	>LL0000	Sample	Chelsea
Validation Rule		Input mask	-
Field no	1.2	Field no	1.6
Field name	Fname	Field name	Gender
Primary Key	No	Primary Key	No
Data type	Text	Data type	Text
Field size	11	Field size	6
Format	-	Format	Male/Female
Decimal	-	Decimal	-
Sample	Roy	Sample	Male
Input mask	-	Input mask	-
Field no	1.3	Field no	1.7
Field name	Sname	Field name	DBirth
Primary Key	No	Primary Key	No
Data type	Text	Data type	Date/Time
Field size	9	Field size	-
Format	-	Format	Medium Date
Decimal	-	Decimal	-
Sample	Armstrong	Sample:	29 Feb 80
Input mask	-	Input mask
Field no	1.4
Field name	Street
Primary Key	No
Data type	text
Field size	21
Format	-
Decimal	-
Sample	1 Nick Lane
Input mask	-
Table no	2
Table Name	Room
Purpose	To store room data
Field no	2.1	Field no	2.2
Field name	Room No	Field name	Phone
Primary Key	Yes	Primary Key	No
Data type	-	Data type	Text
Field size	3	Field size	4
Format	-	Format	-
Decimal	-	Decimal	-
Sample	100	Sample	2100
Input mask	000	Input mask	-
Validation Rule	1{0}3
Table no	3
Table name	Tutor
Purpose	To store Tutor data
Field no	3.1	Field no	3.3
Field name	TutorNo	Field name	TSname
Primary Key	Yes	Primary Key	No
Data type	Text	Data type	Text
Field size	6	Field size	7
Format	-	Format	-
Decimal	-	Decimal	-
Sample	BD1111	Sample	Plummer
Input mask	LL0000	Input mask	-
Validation Rule		Field no	3.4
Field no	3.2	Field name	RoomNo
Field name	TFname	Primary Key	No
Primary Key	No	Data type:	Text
Data type	Text	Field size	3
Field size	7	Format	-
Format	-	Decimal	-
Decimal	-	Sample	100
Sample	Magda	Input Mask	-
Input mask	-
Table No	4
Table Name	Course
Purpose	To store course data
Field no	4.1	Field no	4.3
Field name	CourseNo	Field name	Courseyear
Primary Key	Yes	Primary Key	No
Data type	text	Data type	Text
Field size	6	Field size	3
Format	-	Format	-
Decimal	-	Decimal	-
Sample	CX1342	Sample	One
Input mask	>LL0000	Input mask
Validation Rule		Validation Rule
Field no	4.2	Field no	3.1
Field name	Coursetitle	Field name	TutorNo
Primary Key	No	Primary Key
Data type	Text	Data type
Field size	25	Field size
Format		Format
Sample	GCSE maths	Decimal
Input mask	-	Sample	BD1111
Table No	5
Table Name	Enrol
Field no	1.1	Field no	5.3
Field name	StudentNo	Field name	Fees
Primary Key	No	Primary Key	No
Data type		Data type	Number
Field size		Field size	Double
Format		Format	Fixed
Decimal		Decimal	2
Sample		Sample	300
Input mask		Input mask
Validation Rule		Validation Rule
Field no	4.1
Field name	CourseNo
Primary Key	No
Data type
Field size
Format
Decimal
Sample
Input mask
Validation Rule

Student Number:                                          Surname: Fname:                                                             Area: Street:                                                                    DOB: Gender:

Student:

 

Tutor Code:                                           Room Number: TFName:                                                   TSname:

Tutor:

 

Course Code:                                            Tutor Number: Course title:                                                   Course year:

Course:

 

Room Number:                                        Phone:

Room:

 

Student No:                                        Course Code:                                                   Fees:

Enrolment