After Creating the Database Schema, Use Insert, Update, and Delete Commands to Populate the Tables with the Following Information. Notice That the Values for the Primary Key Columns (Classid in the Classes Table,

Only available on StudyMode
  • Download(s) : 244
  • Published : December 24, 2012
Open Document
Text Preview
After creating the database schema, use Insert, Update, and Delete commands to populate the tables with the following information. Notice that the values for the primary key columns (ClassId in the Classes table, AdvisorId in the Advisors table and StudentId in the Students table) are not listed. It is assumed that they will be “Identity” or “Auto-increment” columns and the DBMS will determine the proper identity value when the row is inserted. You may have to either modify your original data types for some of your table columns or modify the values below to fit your data types. For example, if you defined your gender column to be a 1-character column, you will need to use the values M or F instead of Male and Female. The IsActive column is another column that may require different values, depending on the type of DBMS used.

Table of Contents
Introduction4
Students5
Advisors7
Classes9
Students_Classes11
EER Model14
Modification of class15
Modification of Students Table17
Data Entered In Classes18
Data Entered in Advisor Table20
Data entered in Students Table22
Deleted Investments from the system23
Change of birthdate and GPA24
Question #126
Question #227
Question #328
Question #429
Question #530
Question #631
Question #732
Reference:33

Introduction

In this document there will be an illustration of how to make tables and connecting them with the relationship in a diagram. This information will be worked in MySQL and below will have screenshots to help understand the process. Also throughout this document that will be data types listed some of these are Bit, Int, VarChar, Decimal, and Date (Kinter, 2012).

Students

The students table is the first table that will be inserted in MySql. Once the application is up and running it is important to include the StudentID, FirstName, LastName, Birthdate, Gender, StartDate, GPA, IsActive, Bio, and AdvisorID. The StudentID is the primary key for this table and it is important to know that the AdvisorID is the foreign key. Below is the screen shot of the table called Students:

1. First insert the table name.
2. Under column name add
a. Each name listed above
3. Fill in the datatype
4. Check the PK for the StudentID
5. Check NN for the rest
6. Hit Apply
Next the screen will show the SQL Script and this is what is looks like: CREATE TABLE `CTU`.`Students` (
`StudentID` INT NOT NULL AUTO_INCREMENT ,
`FirstName` VARCHAR(30) NOT NULL ,
`LastName` VARCHAR(30) NOT NULL ,
`BirthDate` DATE NOT NULL ,
`Gender` BIT NOT NULL ,
`StartDate` DATE NOT NULL ,
`GPA` DECIMAL(3,2) NOT NULL
`IsActive` BIT NOT NULL ,
`Bio` VARCHAR(100) NOT NULL ,
`AdvisorID` INT NOT NULL ,
PRIMARY KEY (`StudentID`) );

Advisors

In this table we will call it Advisors and will plug in the following information. AdvisorID (primary key), FirstName, LastName, and EmailAddr. Please reference the screen shot below:

1. Insert table name (Advisors).
2. Insert information into Column Name.
3. Insert the Data type.
4. Check the Primary Key (AdvisorsID).
5. Check NN on every column name.
6. Hit Apply
Once you hit apply the SQL Script will pop up and hit Apply again and the table is saved. Script for Advisors is listed below: CREATE TABLE `CTU`.`Advisors` (
`AdvisorID` SMALLINT NOT NULL AUTO_INCREMENT ,
`FirstName` VARCHAR(30) NOT NULL ,
`LastName` VARCHAR(30) NOT NULL ,
`EmailAddr` VARCHAR(60) NOT NULL ,
PRIMARY KEY (`AdvisorID`) );

Classes

Next, we will do the same thing and add a new table called Classes. In classes we will have a primary key called ClassId. Listed under that will be ClassCode, ClassName, and Description. View screen shot below to understand the next step:

1. List Table Name (Classes)
2. Under column name insert what is listed above.
3. Fill in the Data Type.
4. Check the primary key box for (ClassID)
5. Check all Column with NN
Once...
tracking img