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