Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
data base assignment btec hnd diploma uk
Typology: Study Guides, Projects, Research
1 / 56
This page cannot be seen from the preview
Don't miss anything!
Data base assignment LO1: the Scenario: I am employed as a Database Developer for a large IT consultancy company, which has been approached by ABC HORIZON university. Due to the growth of the number of students, FPT want to expand their systems to deal with managing the university. I am tasked to select one of systems below to develop database for FPT university. In this report, my desire is developing student score management. The Entities in the Company material-professor-room-test-student Activities add materials add room exams(tests) add test date add new student add new professor give report about Students marks display report material and their teacher give administrative data document for all entities of our database Explain the attributes for each entity: Professor: prof_id-prof_name-prof_certificate Student: stud_id-stud_name-stud_birth-stud_address- stud_phone-stud_gender-stud_grade Material: mat_id-mat_name-prof_id-mat_credit-mat_coast Test: test_id-stud_id-mat_id-test_date-mark-room_id
Because every student can do a many tests but every test must be Solve with one Student Relation one to many between table of room and test Each room can organize a test or more test but every test must be in one room A primary key (primary keyword) is a key in a relational database that is unique for each Record It is a unique identifier, such as a driver license number, telephone number (including area code), or vehicle identification number A relational database must always have one and only one primary key Conditions of 1NF 2NF 3NF 1NF: This is the First Normal Form in which a relation contains an atomic value 2NF: The second normal form used for the normalization process. A relation in 2NF must be in 1NF, and all the non-key attributes depend on the primary key in the Second Normal Form 3NF: It stands for Third Normal Form, wherein if a relation is in 3NF, it must be in 2NF, and there should be no transition dependency Logical Design: Material 1NF 2NF 3NF Mat_id Mat_name Prof_id Mat_credit Mat_coast
Professor 1NF 2NF 3NF Prof_id Prof_name Prof_certificate Test :1NF 2NF 3NF Stud_id Mat_id Test_id Test_date Mark Room_id Student: 1NF 2NF 3NF Stud_id Stud_nam e Stud_birt h Stud_adres s Stud_phon e Stud gende r Stud_grad e Room: 1NF 2NF 3NF Room_id Room_name Room_info Room_capacity_ Normalization is the process of reorganizing data so that it meets two basic requirements: 1_ There is no data redundancy, all data is stored in only one place. 2_Data dependencies are logical, and all related data are stored together. Normalization is important for several reasons, but mainly because it allows databases to consume as little disk space as possible, resulting in increased performance.
[prof_id] ASC )
CREATE TABLE [dbo].[room]( [room_id] [int] IDENTITY( 1 , 1 ) NOT NULL, [room_name] nvarchar NULL, [room_info] nvarchar NULL, [room_capacity] [int] NULL, CONSTRAINT [classpk] PRIMARY KEY CLUSTERED ( [room_id] ASC )
CREATE TABLE [dbo].[student]( [stud_id] [int] IDENTITY( 1 , 1 ) NOT NULL, [stud_name] nvarchar NULL, [stud_birth] nvarchar NULL, [stud_adress] nvarchar NULL, [stud_phone] nvarchar NULL, [stud_gender] nvarchar NULL, [stud_grade] [int] NULL, CONSTRAINT [studpk] PRIMARY KEY CLUSTERED ( [stud_id] ASC )
REATE TABLE [dbo].[test]( [stud_id] [int] NULL, [mat_id] [int] NULL, [test_id] [int] NULL, [test_date] nvarchar NULL, [mark] nchar NULL, [room_id] [int] NULL, ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
USE [University] GO INSERT INTO [dbo].[material] ([mat_name] ,[prof_id] ,[mat_credit] ,[mat_coast]) VALUES (<mat_name, nvarchar( 30 ),> ,<prof_id, int,> ,<mat_credit, nvarchar( 50 ),> ,<mat_coast, int,>) GO
USE [University] GO INSERT INTO [dbo].[professor] ([prof_name] ,[prof_certificate]) VALUES (<prof_name, nvarchar( 30 ),> ,<prof_certificate, nvarchar( 50 ),>) GO
Table of student :
