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,...

1 answer below »

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)

  1. 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
  2. Create tables for your entities – you should have tables for STUDENT, SUBJECT, ENROLMENT and LECTURER
  3. Define a primary key for each table
  4. Create fields (and select an appropriate data type) for your attributes.
  5. Save your database definition
  6. 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

  1. 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)
  2. 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
  3. Define an index on the Lecturer table on the Lecturer-name field. The index should be nonunique
  4. Save your work in your SAS database
  5. 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

  1. Count the number of rows in the Customers Table
  2. Display all the customer rows, ordered by city
  3. Update customer Id 1 to set the new ContactName to ‘Albert Smith’ and the City to ‘Bonn’
  4. Do a JOIN on Orders and Customers to show the OrderId and Customer Name for all orders
  5. 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

  1. 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
  2. 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
Answered Same DayDec 15, 2019ICTDBS502Training.Gov.Au

Answer To : Lab 1. Logical database design Description of requirements for student administration system Design...

David answered on Dec 24 2019
57 Votes
ICANWK503A – Install and Maintain Valid Authentication Processes
    
    
    a
RTO No: 20829
CRICOS Provider No: 02044E
ABN: 41 085 128 525
STUDENT COPY- CR
    Assessment Workbook
    Unit Details
    ICTDBS502 – Design a database
    Assessment Requirements
    This unit requires
each student to undergo adequate practice and preparation prior to undertaking the assessments in a classroom/simulated environment or an actual job workplace (formative).
Students are required to complete ALL assessment tasks listed below in order to be deemed “Satisfactory” in this unit of competency.
    Assessment Methods
(Three Assessments for this unit)
    1. Practical Observation
    Assessment type:
Laboratory Exercises
        2. Written Test
    Assessment type:
Multiple choice/short answe
        3. Project / Report
    Assessment type:
Short answe
Written Report
        
    
    
    Document Version 1.1. Issued July 2017
    Unit Summary
    The unit objectives, prerequisites, corequisites and other pertinent information about this unit is described at https:
training.gov.au/Training/Details/ICTDBS502
General Instructions for Students
READ THESE INSTRUCTIONS CAREFULLY BEFOR YOU BEGIN!
· You should download the softcopy of this document and use it as a template for your own assignment submission.
· You will be directed to save screen prints and/or write down answers to questions when you complete this workbook. You need to include those screen prints/answers inline (i.e. at the co
ect place in the document) so your assignment can be assessed. If there are sample screen prints included in the document, you need to delete those and include your own screen prints. Do not submit your assignment without all the required screen prints/documents, as you will be marked NYC and need to resubmit the assignment
· To complete the practical tasks, you will need to consult the Learner Resources and/or Lab Guide. These are available in Moodle
· Keep your softcopy of your assessment/workbook (in case you need to resubmit it)
· Answer all parts of the assessment
· Type all your answers (do not handwrite)
· Do not cheat. Anyone caught cheating will automatically be marked Not Yet Satisfactory for this unit. There are NO EXCEPTIONS to this rule.
In order to achieve competency, all assessment tasks MUST be completed to a satisfactory level
Note:
All references made to workplace and or employer, are directly related to the trainers and facilities at VIT.
PRACTICAL OBSERVATION – INFORMATION FOR STUDENTS
Laboratory Exercises
This information is to be handed to each student to outline the assessment requirements
This assessment is to be undertaken in an actual job workplace or a classroom/simulated environment. Read the...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here