i attch the file
Create a Database of your own contentCP 270 - Advanced Database Design & SQL Assignment 4 20% of your final mark (rubric at the end of this document) The assignment spans 2 weeks. At the end of week 1 (BEFORE the beginning of Week 2’s class) you will submit, via email, the rough draft documents from “FIRST STEP” below for 5 marks of the 40 total. This assignment can be done individually or in teams of 2 students. Everyone in the team will submit all final documents. For the initial draft documents, only one person emails me and identifies who’s in the group. Also, identify in your final submission who you worked with and how you would rate their participation in the assignment on a scale of 1 to 5; 1 meaning they did not participate (and you did all the work), 5 meaning you both worked on it equally. I will take your comments into consideration when grading. THE DATABASE NAME MUST BE YOUR NAME + subject eg. [gjones_Cookbook]. If it’s a group project, name your database with both your usernames (e.g [gjones_vsmith_Cookbook] ). If you are in a group, after the database is created, you will need to email me who created it and who your partner is so that I can give the other person access to the database. If they don’t have access, how can they help do any of the work! DO NOT create a “Student – Courses” database, and NOT any other database used in this course as of yet. DO NOT simply copy something off the Internet. You are trying to learn, not just hand in a ‘correct’ assignment. Make a database of something personal to you. Bird watching database, plant watering schedule,… Must have at least 4 tables (with at least a few fields in each – probably a main table with a couple lookup tables) and they must be related via primary & foreign keys with the correct relationships defined (Cardinality). Remember that tables in a database are for efficiently storing data. They are not designed for how groups of data will be retrieved. GO THROUGH THE FORMS OF NORMALIZATION. Example: you wouldn’t create a table of province because you want to “search out“ people by what province they are in… you create a province table because there are many people who will live in a province, which means “Ontario” will be repeated throughout the people rows – it should be separated to a “lookup” table because it is a repeating value. If you have issues, email me the details of what’s wrong. What design issues are you having? (If you have an error, what did you click?) The basic steps to creating a database FIRST STEP: Think of an idea! Nothing extravagant – keep it simple, you will see that it will grow without even trying! Use Excel or something similar to write out the data you want to collect – the “one-row” raw values. Create several rows of data so you’ll see the repeating values that will create table relations (or “lookup” tables). Use these as your sample data when your database is completed. Watch some of the Normalization videos like Channel5567’s, Caleb’s or the LinkedIn.com videos if needed. THIS IS THE HARD PART, take lots of time on this…. Start breaking down your raw rows into new entities according to the forms of normalization, YouTube videos do this if you want more examples. THIS IS WHAT YOU WILL SUBMIT AT THE END OF THE WEEK. I will probably have comments that will change your design so don’t jump ahead and start creating the database. You’ll just have to change it later! SECOND STEP: Diagram your database using an ER Diagram or Model (a Database Diagram is not acceptable for this). Use any of the app options mentioned in the “Class 10” document. If you have a choice of either Crow's Foot notation and Chien style, either is fine. Chien is the style of my example Cookbook diagram whereas the Crow's foot style Visio template is more of a technical style (doesn't have the diamond "relationship" between entities). Remember to name your tables using singular form. i.e. person not people, recipe not recipes. I’ll let you decide if you want to capitalize them or not, just be consistent. As you are drawing your ER Diagram, review your work. This is a time to have a second look at how you are breaking down your entities. Remember that the attributes of an entity are the column names of your table and NOT values that are stored in them. Example: FirstName is an attribute and attached to the entity in the diagram. “Gord” is a value – not written in the diagram. You will hand this ER Diagram/Model in via the final Blackboard assignment. THIRD STEP: create your database using SSMS. · Create a database using right-click\New Database with a name that is prefixed with your userId (i.e. gjones1_cookbook) · On the Options page ensure the Recovery Model to “Simple” · You don’t have to define a special schema to hold your tables… the default dbo is fine. · Create the tables you need using the “New Table” designer (remember if you need to change the design, right-click\Design…). Also remember that the activity we did in the previous class had instructions on how to allow SSMS to edit tables without the error message about “tables would be dropped and recreated”. · Use column data types that make sense to the data in them. INT, Numeric, Date, Datetime, Varchar, Bit, … · Fill in each column’s Description property as documentation (why is this column here?) · Make an Identity column type primary key column on each table if you don’t have a “natural” primary key. · Appropriate column name · Right-click the column\”Set Primary Key” (notice that “Allow nulls” will automatically be unchecked – to protect the primary key!) · In the Column Properties expand “Identity Specification” and set “(Is Identity)” to Yes. Leave the Increment and seed at 1. This will have SQL Server auto-increment a new key value every time a new record is inserted in the table. · Mark columns that should allow NULL (or not) as the data dictates. · There must be at least one column with a Constraint defined is one of your tables. · i.e. ExperienceLevel column must be 1,2,3 (See screenshot below) · There must be at least one column with a Default value defined. · i.e. createdDT is current datetime (See screenshot below) · Create a SQL Database “Database Diagram” and use it to create relationships. If you have issues doing this let me know with a screenshot. · When you click the Database Diagram node you will be asked if you want to install support for this… click Yes. · Then right-click the node and create a new database diagram. · Add your tables to the diagram. · Drag you table primary key over matching foreign key(s) to create the relationships (see screenshot below) · Edit Rows of your tables to populate them with data using right-click Edit Top 200 Rows · If you have your database relationships setup, SQL is going to force you to populate your tables in the “correct” order! What values do you need to enter in your Foreign keys? · Fill tables with appropriate sample data (from your original Excel file, add more if you like). Have this Sample data demonstrate any one-to-many relationships your tables have. (Example: In our cookbook database an author has more than one recipe, so there are multiple recipe records for one author.) · Create a View that contains all your tables and display all columns but not the IDs/keys…. Just the “meaningful” data in the tables. A view you would use in your application to display the data to a user. · In a Word document, describe to me your database and why it is structured the way it is. Example: · My database is for a cookbook. The main table is the Recipe table. An Author could write zero or many recipes so that is why the author is not part of the Recipe table. It is not necessary to have the author’s name broken down into first & last name. Since Recipes have many Ingredients, the Ingredients are in their own table. I also want the cookbook to be searchable by ingredient, this is another reason to have the ingredients be in their own table. Many Recipes could be in a Category, so the categories have been broken out into their own table as well. Recipes may or may not have a multimedia file(s) attached to them, so they are separated into their own table as well. There is a Default Value set on the [createdDT] column and a check constraint that makes sure that more than 100 characters have been entered as the Ingredient name. See rubric below. Submit your ER diagram and the Word document description via blackboard. I’ll see your database on the server. Assignment is worth 20% START YOUR ASSIGNMENT EARLY (this week), email me your “FIRST STEP” and bring it to the next class to ask me questions, 5 mark is just getting this to me in the first week. Late assignments get a zero. RUBRIC Excel file of “Raw” data showing a need for related tables. Will also have your initial Normalized database. 5 marks For both the ER Diagram and the actual database. 35 marks Use correct & consistent naming conventions and case (PascalCase or camelCase) = ERD; 1 mark. DB; 1 mark Database created = DB 1 mark (database creation only) Tables created = ERD; 1 mark. DB 1 mark Database is normalized to “3rd normal Form” = ERD 6 marks Table columns have correct data types. Columns have description property populated in the tables = DB 6 marks Create the Database Diagram on the SQL database = 1 mark Primary keys and foreign keys are defined per table and related correctly = ERD; 2 marks. DB 2 marks Define at least one column to have a default value = 1 mark Define at least one constraint = 1 mark View that displays all data at once = 4 marks Fill tables with appropriate sample data. Have this Sample data demonstrate any one-to-many relationships your tables have. (Example: In our cookbook database an author has more than one recipe) = 4 marks Database description – Word Document = 3 marks TIPS….. i) REMEMBER to turn off (uncheck) the SSMS option that stops you from being able to edit & save in Table designer…. Tools\Options\Designers…. “Prevent saving changes that require table re-creation” ii) Set a Default Value on a Column Set the CreatedDate column to automatically be “GetDate()” when a new record is inserted iii) How to create a Column Constraint. Notice that in the Expression you can put anything that is a predicate (equates to True of False). This check will be executed any time a value in this column is changed. Database diagram Example of using a Database Diagram and create a relationship between [phoneType].[phoneType] and [authorPhone].[phoneType]