Proj8_Security - Project 8 Security and SQL DCL IT 3233...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Project 8 – Security and SQL DCL IT 3233 – Database Design and Implementation Dr. Cheryl Aasheim 12/6/11 Group 8: Jenna Sicard Marshall Brown Ted Voyles Introduction: In Projects 1-7 your team completed an initial conceptual and logical data model (ER diagram, data dictionary, relational schema and 3NF relations) and created the database for the myTunes. With the design phase complete and the implementation phase started, your team is ready to provide security enhancement to the myTunes database before it is deployed. You will need to write triggers to automate logging on important data fields, create views of the data for different user groups and provide appropriate access to data in the database to user groups. In addition, you will add some constraints to protect data integrity. Task 1: Add Constraints (24 points) Put all SQL commands written in this section in a Word document. Label each according to the problem number to which it corresponds. Write the SQL command to add the following constraints that: 1. Ensure that the price of a song is between zero and $4. Name the constraint ck_song_price . ALTER TABLE SONG ADD CONSTRAINT ck_song_price CHECK ( price > 0 and price < 4 ); GO 2. Ensure that customer email addresses are valid (have an @ and a . in the correct order). Name the constraint ck_customer_email . ALTER TABLE Customer ADD CONSTRAINT ck_customer_email CHECK ( email LIKE '%@%.%' ); 3. Ensure that the device type is from the correct set of possible types (open the table to see the set of device types) in the device table. Name the constraint ck_device_type . ALTER TABLE Device ADD CONSTRAINT ck_device_type CHECK ( DeviceType IN( 'MP3' , 'Tablet' , 'Phone' )); 4. Ensure that the price of an album is at least zero. Name the constraint ck_album_price . ALTER TABLE Album ADD CONSTRAINT ck_album_price CHECK ( Price >= 0 ); 5. Ensure that the password of a customer has at least one number in it. Name the constraint ck_customer_password .
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ALTER TABLE Customer ADD CONSTRAINT ck_customer_password CHECK ( Password LIKE '%[0-9]%' ); 6. Ensure that the purchase date is later than November 1, 2006 as this is the date the company opened. Name the constraint ck_purchasehistory_date . ALTER TABLE PurchaseHistory ADD CONSTRAINT ck_purchasehistory_date CHECK ( DatePurchased >= '2006-01-11 00:00:00.000' ); Task 2: Create Triggers (20 points) Provide the TSQL for all triggers and all CREATE TABLE commands created in this section in a Word document. Label each according to the problem number to which it corresponds. Part 1: Create a trigger to audit updates of price changes in album table 1. Create a table called audit_change_album_price that will record all of the updates made to the price column in the album table as well as the user that updated that price, the time the update was made (all columns in this table are NOT NULL). CREATE
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 02/13/2012 for the course IT 3233 taught by Professor Aashiem during the Fall '11 term at Georgia Southern University .

Page1 / 7

Proj8_Security - Project 8 Security and SQL DCL IT 3233...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online