Here are the instructions from the homework im struggling with:
Please create at least 4 tables with the instructions
Chap 2 - What is Database Design? In an attempt to better understand just how tables work and are used, we will take a short look at Database Design. Once we have designed a small database, we will understand just how a database works. Then we will start looking at the SQL commands, in Chap 3. - Getting Started with Database Design The first thing that you need to do is come up with a list of entities, nouns. These will be our tables. So Step \# 1 is to Brainstorm what nouns are part of our Application. - Database Design Steps Let's say we are going to design a database for a small college. Step =1 would be to think of all the nouns that are part of a college. Nouns: Students, Instructors, Courses, Sections and Schedules. These nouns need to be larger entities that have many properties. For example a Student is an Entity, but a firstname is not. Firstname is just a simple noun. A student has multiple parts. Step 22 - Once we have our major Entities determined(these will become our tables), we next decide on properties/columns for each table. Rules for naming Columns. 1.) Column names cannot have spaces 2.) Column names cannot contain special chars like " or % 3.) Column names cannot be key words like 'FROM' or 'SELECT' The Students Table will have a Firstname, a Lastname, an addr an email, a major and a gpa. We will also add a Primary Key, StudentID. So the Students Table will have 7 columns. The Instructors Table will have a Firstname, a Lastname, an Address, an email, and an office. We will also add a Primary Key, InstiD. So the Instructors Table will have 6 columns. The Courses Table will have a CourseName, a Description and CreditHours. We will also add a Primary Key, CourselD. So the Courses Table will have 4 columns. The Sections Table will have a CourselD, a TimeDay, a roomNo and an instructor. We will also add a Primary Key. CRN. So the Sections Table will have 5 columns. The StudentSchedule Table will have a StudenytiD and a CRN. So the StudentSchedule Table will have only 2 columns. Step $3 - Now we need to decide on the datatypes for each column in each table. Let's start with the Students Table. The Studentio column is probably going to be something like char(9). At CTC your StudentiD is char(9). Exactly 9 chars and no more or no less. The firstname and lastname columns will be varchar(30), variable length character. since we do not know how long each firstname or lastname will be. Address and email can also be varchar(40). Major may be char(4). Each major code is always 4 characters. And gpa will be numeric. Step 44 - Next we have to determine which columns are required and which are optional. Required columns will be 'not null' and optional columns will be 'null'. Not null means a column has to be given before a new row can be added to the table. Null means that the data is not needed when a row is first created, it can be added later. Si for the Students table, StudentiD is 'not null', required. Firstname is 'not null', required. Lastname is 'not null', required. Address is 'not null', required. Email is 'null', optional. Major and gpa are also 'null', optional. So if a user wants to enter a new Student, they must provide a Studentid(sometimes the database generates this), a firstname, a lastname and an Address. Email, major and gpa can be added later. Step $5 - And finally we need to decide on a Primary Key. For our Students Table that will be the StudentiD. Below is a completed Students Table design: Students Table Summary Database Design Steps Step \#1 - determine tables(major entities) Step \#2 - For each table, determine which columns are needed. Step \#3 - for each column determine the datatype needed for that column. Step \#4 - for each column determine 'not null'(required) or 'null'(optioanls). Step \#5 - for each table decide on a Primary Key
