Case Study 6:MS SQL SERVER 2005 – Management Studio – Create tables via SQL Script MUSICIANS EXAMPLE

MS SQL SERVER 2005

Management Studio – Create tables via SQL Script

MUSICIANS Database EXAMPLE

The following guide shows how to convert an entity relationship diagram (ERD) for MUSICIANSto a SQL database using SQL scripting.

SQL Coding Exercises
ER Modelling / Exercise 1 – Review the MUSICIANS ERD.
SCHEMA - PHYSICAL / Exercise 2 –Loading the physical schema for MUSICIANS ERD.
SCHEMA -LOGICAL / Exercise 3 –Create the logical schema for MUSICIANS ERD.
SQL / Exercise 4 –SQL commands.
ACCESS SQL PROJECT / Exercise 5 – workstation—server database
MODEL SOLUTIONS

Provided for 1 and 2 within exercises.

FOR ICA PURPOSES
THE ERD MUST BE FURTHER DEVELOPED.
PROVIDE SQL CODE FOR NEW TABLES.
CONSTRAINTS TO FULLY UTILISED WERE POSSIBLE.
PROVIDE SQL CODE TO INSERT MEANINGFUL DATA INTO NEW TABLES
PROVIDE ALL SQL CODE FOR LOGICAL VIEWS.
ALL SQL DDL and DML CODE EXAMPLES TO BE PROVIDED.
FOR HIGHER GRADE CONSIDER PRODUCING A FULL DEMO GUIDE OF EXCERSISE 5.
THESE SHOULD ALL BE DOCUMENTED.providing evidence in the form of screen shots
  • Data View before
  • SQL Code in Query Analyser
  • Resulting Data View.

ER Modeling

Exercise 1 – Review the MUSICIANS ERD DataModel

SCHEMA - PHYSICAL

Exercise 2 –Loading the physical schema for MUSICIANS ERD.

SQL Server Physical Schema is the set of SQL commands required for Table Definitions.

Once you create a database SQL commands or Script files are loaded into a New Script interface and executed to create the tables for the ERD’s.

As you work through the exercises examine the SQL code provided which has been written in notepad. Also note the SQL code for …

  • the allocation of primary and foreign keys.
  • the validation checks and constraints.
  1. Run MS SQL Server 2005 and create a new database called MUSICIANS. Click on the database in the Object Window pane before executing any of the SQL commands. Failure to do so means all the created tables etc will appear in the master database.
  1. Click on the link below to automatically load the SQL code into a New Query window . The SQL code builds the above ERD and is also provided in the appendix of this document.

sql.QA.musician.create.sql

Also provided are the drop commands to remove the tables sql.QA.musician.drop.sql

  1. Examine the SQL code structure and contents of each table. Execute the SQL Code by clicking . Expand the MUSICIANS database – Tables and note the tables have been created.
  1. Use the MUSICIANSERD to check the structure and contents of each table, as well as the relationships between them. Click on Database Diagrams and create a new diagram to represent the ERD.
  1. Click on the link below to automatically load the SQL code into a New Query window . The SQL code inserts data into the tables and is also provided in the appendix of this document.

sql.QA.musician.insert.sql

  1. Examine the SQL code structure and contents of inserting sample data into each table. Execute the SQL Code by clicking . Expand the MUSICIANS database – Tables and note the tables have data inserted.

Select and use the following commands to examine the content of each table.

SELECT *

FROM tablename

  1. The SQL code forsql.QA.musician.create.sqlhas been written in notepad. However MS SQL Server 2005 can generate the SQL Script Code itself. Right click the database MUSICIANS and Script Database > Create To > New Query Editor Window . Compare the auto generated code with that written in notepad. Compare, contrast and familiarise yourself with the SQL code.
  1. The SQL code forsql.QA.musician.create.sqlhas been written in notepad. The SQL code in sql.QA.musician.create.EM.sqlhas been auto generated in MS SQL – Enterprise Manager. Compare, contrast and familiarise yourself with the SQL code.

SCHEMA - LOGICAL

Exercise 3 –Derive all the required logical schemas for MUSICIANSERD.

SQL Server logical Schema are the SQL commands required for View Definitions.

These commands are loaded into a New Script interface and executed to create the views derived from the ERD’s.

Using the Create View command write the SQL code which will produce the determined logical views for the case study.

a)Use Management Studio to create the views

b)Write your own SQL Code for UniSpares view schemas in Notepad / MS Word.

  1. Load and execute in MS SQL Server 2005 – Management Studio.
  2. Validate your code and remove any errors.

SQL

Exercise 4 –SQL commands

SUPPORT: SQL Lecture Material

The following online SQL lecture material provides an overview of SQL DDL and DML code. You may refer to them to overview any SQL code syntax covered in the above case studies.

They will assist in tackling the SQL guides & case studies and are provided as part of the Database & SQL lecture booklet.

Scm-Intranet / SQL User Guides
powerpoint / SQL Overview
Word / SQL Concepts

Demonstrate your knowledge of SQL by providing SQL code for the following statements using the above database.

Consider providing evidence in the form of screen shots

The SQL code should be based on typical queries which would be required for this case study.

DDL commands
  1. CREATE INDEX
  2. CREATE VIEW
  3. CREATE TRIGGER
/ DML commands
  1. SELECT
  2. SELECT & FROM
  3. WHERE CLAUSE
  4. ORDER BY
  5. DISTINCT
  6. BETWEEN and LIKE
  7. IN and NOT IN
  8. JOIN Tables
  9. CREATE TRIGGER
  10. DELETE
  11. UPDATE

ACCESS - SQL

Exercise 5 –Produce a MS Access and MS SQL Server project based on this case study

MS SQL Server Project - linking MS SQL server (physical tables) to MS Access workstation (logical views)

1

Mansha NawazMUSICIANSCase Study 6: