Lab 1. Logical database design
Description of requirements for student administration system
Design a database that will support the following requirements:
Stores information about students, subjects, subject results (or enrolments), and lecturers
Students can enroll in many subjects, and a subject can have many students enrolled in it
For students, I need to keep the Student ID, surname, first name, and date of birth
For subjects, I need the subject code, subject name and lecturer id.
For lecturers, I need a lecturer id, firstname and surname
A student can attempt the same subject many times. A student attempting a subject is called an enrolment
For each), I need to record the enorlment id, student id, the subject code, the year the student enrolled in that subject, and the result obtained. You can assume that a student cannot enroll in the same subject twice in the same year.
One lecturer can teach many subjects, but a subject is only taught by one lecturer Once you have done the ER Diagram, include it in your workbook as evidence to show you have completed the lab
Lab 2. Database implementation
To prepare for this lab, you should read Lesson 2 and Lesson 4 of Database Administration Fundamentals. You should also view the SQL Server 2008 Tutorial for Beginners playlist, especially the videos on how to create a database (second in the playlist) and how to create and work with tables (third in the playlist)
Create a database called SASnnnnn (where nnnnn is your student number. For example, if your student number was 12345, your database would be called SAS12345
Create tables for your entities – you should have tables for STUDENT, SUBJECT, ENROLMENT and LECTURER
Define a primary key for each table
Create fields (and select an appropriate data type) for your attributes.
Save your database definition
Take a screen shot of your table definitions, and include them in your workbooks as evidence you have completed the labs
Lab 3. Implementing constraints and indexes
The SQL Server Tutorial for Beginners playlist has a number of helpful videos for this lab, such as the fifth video, the sixth video and 35th video
Define a foreign key constraint on the ENROLMENT table, so that the subject code on the ENROLMENT table is acting as a foreign key to the subject code on the SUBJECT table (note: you should have defined the subject code on the SUBJECT table as a primary key in the previous lab)
Define a check constraint on the subject result field on the ENROLMENT table to check that the result for the subject must be one of C or NYC
Define an index on the Lecturer table on the Lecturer-name field. The index should be nonunique
Save your work in your SAS database
Take screen shots of your index definition, foreign key constraint, and check constraint, and include them in your workbook as evidence of having completed the labs
Lab 4 Querying and reporting data
Write SQL Queries that perform the following functions
Count the number of rows in the Customers Table
Display all the customer rows, ordered by city
Update customer Id 1 to set the new ContactName to ‘Albert Smith’ and the City to ‘Bonn’
Do a JOIN on Orders and Customers to show the OrderId and Customer Name for all orders
Generate a report that shows the customer count for each city. (Hint: Use the GROUP BY feature). Make sure you override the default column heading to have a heading ‘Customer Count’ to improve readability of the report
Lab 5 Securing and backing up your database
Before you begin, read the Microsoft documentation on database backups
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server
Perform a full backup of your database SASnnnnn (where nnnnn is your student number). Call the backup file you create SASnnnnn.bak and save the backup file in the C:DBBackup folder
Once you have performed the backup of your database, take a screenshot of the backup file and copy it into your workbook. See example below