Question

Need help understanding and choosing data types for my ERD crowsfoot tables. Also if you see any other errors please point out need assistance bad to learn it.

Student Catalog Studentio harta SectionjNOT NULL Description clessType QuizNum Professo Profofiice

Answer 1

Let us look at each of the following tables one by one:

1.Student :

-->StudentId-This attribute acts as the primary key key for the student table.It is used to store a student's id in it.Since,it is a primary key,it contains unique values for different students,i.e each student will have a unique StudentId value and no two or more students will have the same StudentId value.It is defined with an int datatype as this attribute contains id in the form of numbers only and hence it is suitable to use the int datatype.Also,since,its a primary key it can't have null values.Every student should have a StudentId.

---->FirstName-This attribute contains the first name of the student.It is defined using a varchar datatype as the first names of the student will consist of alphabetical characters and the datatype,varchar is suitable to define an attribute containing alphabetical and other characters from the keyboard.The size of varchar will be 24 as this attribute is defined as varchar(24).This means that the maximum size of the first name will be of 24 characters.This attribute is also defined with a not null constraint,which means that this attribute can't have an empty or a null value.Hence,it is necessary to give every student a firstname.

---->LastName-This attribute contains the last name of the student.It is defined using a varchar datatype as the last names of the student will consist of alphabetical characters and hence the datatype varchar is suitable to define this attribute.The size of varchar will be 24 as this attribute is defined as varchar(24).This means that the maximum size of the last name will be of 24 characters.This attribute is also defined with a not null constraint,which means that this attribute can't have an empty or a null value.Hence,it is necessary to give every student a lastname.

---->Major-This attribute contains the course specialization or major of the student.It is defined using a varchar datatype as the major of the student will consist of alphabetical characters and hence varchar is suitable to define this attribute.The size of varchar will be 24 as this attribute is defined as varchar(24).This means that the maximum size of the major will be of 24 characters.This attribute is also defined with a not null constraint,which means that this attribute can't have an empty or a null value.Hence,it is necessary to give associate every student with a Major value.

Now,let us see how to we actually create the student table in a mysql or an oracle database:-(Below is the code to do so,some changes can be possible depending on the version and software used but the concept remains the same)

create table Student(
StudenId int primary key,
FirstName varchar(24) not null,
LastName varchar(24) not null,
Major varchar(24) not null);

2.Catalog:

---->CourseId-This attribute contains the course id value of a particular course.It is defined with an int datatype as this attribute contains CouseId which is in the form of numbers only and hence it is suitable to use the int datatype.It is defined with a primary key constraint.Also,since its a primary key it can't have null values.

---->Semester-This attribute contains the semester value of a particular course,i.e in which semester the course is being taught.It is defined with a varchar datatype as this attribute contains Semester which is in the form of alphanumeric or other characters only and hence it is suitable to use the varchar datatype.It is defined with a primary key constraint.Also,since its a primary key it can't have null values.It can contain maximum 8 characters because the maximum size of varchar during time of definition of this attribute is 8.

Note:-In this table as we can see that there is a pair of primary key at the same time.Hence,the combination of the entire primary key pair will have a unique value,what I mean to say is multiple CourseId can have same values or multiple Semester can also have the same value but,their combination should always contain a unique value.Lets take an example to understand this better:-

Consider the combination of (CourseId,Semester)values respectively-('CS102',7),('CS102',8),('CS102',7)(declared again),('CS103',7)

Here we can see the pairs ('CS102',7),('CS102',8),('CS103',7) are legal or valid values because although one of the values are same in each of them but as an entire order they have different pairs of values('CS102',7) and ('CS102',8) contain the same course id but they have different semester values and thus,is a legal value whereas ('CS102',7) when declared again will give an error because a similar value pair of couseid and semester is already defined and the pair of primary key will always contain unique values.Hence,we can say no two courseId,semester value will contain the same value,since they are a pair of primary key in the table.

---->Section-This attribute contains the section value of a particular course.It is defined with an varchar datatype as this attribute contains Section which is in the form of alphabetical characters only and hence it is suitable to use the varchar datatype.

---->Description-This attribute contains the description and other brief information about a particular course.It consist of alphabetical and it may also contain numeric characters in the description and thus,this attribute is defined with a varchar datatype.

----->ClassType-This attribute contains the class type of a particular course and semester i.e if the course is a theory or a practical class(just a way of interpreting it may even contain different values but for explanation purpose mentioned this one,although this will take a varchar value since it always will consist of alphanumeric values)It consist of alphabetical and it may also contain numeric characters and thus,this attribute is defined with a varchar datatype.

---->CreditHours-This contains the credit hours associated with a particular couse and semester.It can be defined with int value as this attribute consist of numerical values only.

---->Prolloq-This attribute contains prologue of a particular course associated to a semester.It is defined with a varchar datatype because it contains alphabetical characters.(I am sorry if this attribute was read wrong by me as it was not clear)

Lets see how this table will be declared in mysql or oracle database:-

create table catalog(

CourseId int,

Semester varchar(8) not null,

Description varchar(50),

ClassType varchar(24),

CreditHours int,

Prolloq varchar(50));

3.ProfInfo:

---->ProfessorId-This attribute contains the professor id of different professors.This attribute acts as the primary key key for this table,hence it must contain unique and non-empty values.It is defined with an int datatype as this attribute contains id in the form of numbers only and hence it is suitable to use the int datatype.

---->Professor-Declared with varchar character and contains professor's name consisting of non-empty alphabetical values.

---->ProfOffice-Contains the details of a professor's office.It is declared using varchar datatype because it consist of alphabetical and numerical characters.

---->ProfPhone-This attribute contains professors' phone numbers.It is declared using varchar since it may contain alphanumeric characters.(Note-It can also be declared using int if phone numbers contain only numeric values but its always advisable to store such values as varchar.)

Lets now see how this table is declared:

create table ProfInfo(

ProfessorId int primary key,

Professor varchar(48) not null,

ProfOffice varchar(24),

ProfPhone varchar(15));

4.Schedule-

---->CourseId-It is a foreign key referencing to the CourseId value in catalog table catalog and is also acting as a primary key for this table.Declared with a int datatype since it consist of only numbers.

---->Semester-It is a foreign key referencing to the semester value in the catalog table catalog and is also acting as a primary key for this table.Declared with a varchar datatype since it consist of alphanumeric characters.

---->Section-It is a foreign key referencing to the Section value in catalog table and is also acting as a primary key for this table.Declared with a varchar datatype since it consist of alphanumeric characters.

---->ProfessorId-It is a foreign key referencing to the ProfessorId value in ProfInfo table.It is defined with an int datatype as this attribute contains id in the form of numbers only and hence it is suitable to use the int datatype.

---->RoomNo-contains the room number for a particular course,semester and section.It is declared using int datatype since it contains a numerical value only.

---->Days-contains the days for a particular course,semester and section when its going to be held.It is declared with a datetime since it contains a datetime value.

----->Time-contains the time for a particular course,semester and section that is at what time its going to be held.It is declared with a datetime since it contains a datetime value.

Lets now see how this table is declared:

create table schedule(

CourseId int references catalog(CourseId),

Semester varchar(8) references catalog(semester),

Section varchar(8) references catalog(Section),

ProfessorId int references ProfInfo(ProfessorId),

RoomNO int,

Days datetime,

Time datetime,

primary key(CourseId,Semester,Section));

(Note:-here the combination of CourseId,Semester and Section acts as a primary key and their combination is unique and we can't have the same combinations multiple times.)

5.QuizNum-

---->QuizId-It contains a unique quiz id for every quiz which is going to be taken.It is defined as a primary key and so multiple quizzes can't contain same values.It is defined with an int datatype because quizid contains numerical values.

---->QuizGrades-It contains the grades for the quiz.It is defined with a varchar datatype as it contains alphanumeric character values.

Lets now see how this table is declared:

create table QuizNum(

QuizId int primary key,

QuizGrades varchar(10));

6.Gradebook-

---->StudentId-it is a foreign key referencing to the StudentId value in Student table and is also acting as a primary key for this table.Declared with an int datatype since it consist of only numbers.

---->CourseId-It is a foreign key referencing to the CourseId value in catalog table catalog and is also acting as a primary key for this table.Declared with an int datatype since it consist of only numbers.

---->Semester-It is a foreign key referencing to the semester value in the catalog table catalog and is also acting as a primary key for this table.Declared with a varchar datatype since it consist of alphanumeric characters.

---->Section-It is a foreign key referencing to the Section value in catalog table and is also acting as a primary key for this table.Declared with a varchar datatype since it consist of alphanumeric characters.

---->QuizId-it is a foreign key referencing to the QuizId value in QuizNum table.Declared with an int datatype since it consist of only numbers.

Lets see how this table can be created in mysql/oracle database:-

create table Gradebook(

StudentId int references Student(StudentId),

CourseId int references catalog(CourseId),

Semester varchar(8) references catalog(semester),

Section varchar(8) references catalog(Section),

QuizId int references QuizNum(QuizId),

primary key(StudentId,CourseId,Semester,Section));

(Note:-here the combination of StudentId,CourseId,Semester and Section acts as a primary key and their combination is unique and we can't have the same combinations multiple times.)

-----------------------------------------------------------------------------------------