Assignment 1 – Review Database Creation in SQL Server
Objectives
Demonstrate ability to get SQL Server operational and create a database within SQL Server.
This is a review assignment.
Assignment
1. You will need to install SQL Server and SQL Server Management Studio. You can download these at https://www.microsoft.com/en-us/sql-server/sql-server-downloads the Developer Edition will work fine for you.
2. You will use either one of the supplied data dictionaries or one of your own designs to create a database in SQL Server. Your database design (if you do your own) should contain an N:1 and an M:N relationship (with a join table).
https://www.relationaldbdesign.com/database-design/module6/three-relationship-types.php
3. After creating the table structure, you can use the SQL Server diagram tool to create a diagram of your database.
4. Create simple documentation of your database with a data dictionary, SQL code for creation, and database diagram.
You can select one of the database dictionaries below or propose your own database. You may also begin work towards your database project design and use this database in this assignment. Your database should demonstrate at least one of each of the following; 1:1 relationship between entities, 1:n relationship between entities with the correct use of foreign key constraints, n:n relationship between entities with relationship table to correctly represent the n:n relationship.
Bottom Line
- Install the correct version of SQL Server (you will need to research and discuss)
- Create a new database based on specifications (mine or yours)
- The database must have a 1:n and an n:n relationship
- Create a diagram of the database (recommend using SQL Server Management Studio)
- Create a Data Dictionary of the Database
- Put those into documentation
Data Dictionary – Class Advising System
Data Dictionary – Sports Players Statistics
Data Dictionary – Music Database
You will turn in (to the drop box) a word doc or pdf file of the documentation. The documentation should contain
1. Data dictionary (all fields, field types, description)
2. ERD Diagram (with keys shown). SQL Server SSMS will do this.
Information
This assignment covers all the basics of getting started with SQL Server, creating a database, and the basic database objects (tables, keys, etc…).
This is a relatively straightforward review example – it is a good opportunity to get database management up and running.
You can also run the database on Azure, which is freely available to you in Dreamspark as a student. You will need to be able to run SQL Server Management Studio locally. You can use SQL Server Management Studio to connect to the database instance you have created on Azure.
SQL Server Management Studio – This is a client tool that can be used to manage database. It is NOT a database itself, but is simply a tool. You will need to connect to a database to manage it.
SQL Server Instance or SQL Express – You can run an instance of these on your local machine. These are database servers and run as a service on your local machine (or remote) to manage databases running in an instance of the server.
Azure – Azure is a cloud service and will allow you to run an instance of SQL Server in the cloud. This will be required in some later assignments.
Estimated Completion Time
This is a review assignment. This should take experienced users 3-6 hours, for less experienced database users you should allot up to 10-20 hours.
Supporting Lectures
Questions and Answers
Discussion board should be used for any questions or discussion.
External Resources
Here is a simple guide to connecting – just in case you need it. This is a pretty easy install so you should be OK – https://msdn.microsoft.com/en-us/library/ms345318.aspx
Grading Criteria
A well structured database that handles all the requirements is worth full credit.
– Database created
– Database has required elements (1:1, 1:n, n:n relationships)
– Documentation complete (data dictionary, SQL script, Diagram)