Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2013
    Posts
    3

    Unanswered: Can anyone Help me

    Can anyone help me with this. (DATABASE)?
    -Subjects can be set to any courses by semester and school year.
    -A student may enroll one or more subjects per semester and school year.
    -Student is required to fill up the registration form with their name and birthday.
    -Subject has Subject Number, Description, and Unit.
    -Courses has name and course code.
    -College has name and abbreviation.
    -semester has value (example 1st semester).
    -school year has value(example 2013-2014).

    Can anyone help me figure this one out. i already created some tables. my tables are Course, Subject,Student and College. Im confused what attributes should i put. And im not so sure if my tables were correct.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That looks like a neat homework assignment!

    Can you share more of your initial workings with us please?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2013
    Posts
    3

    what do you mean by initial workings?

    Quote Originally Posted by gvee View Post
    That looks like a neat homework assignment!

    Can you share more of your initial workings with us please?
    what do you mean by initial workings?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by KarlJoseph View Post
    i already created some tables.
    Why not start by sharing your CREATE TABLE scripts?
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2013
    Posts
    3

    here is this correct

    Code:
    CREATE TABLE Students
    (
    ID INT IDENTITY NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    MiddleInitial VARCHAR(1) NULL,
    LastName VARCHAR(255) NOT NULL,
    Birthday DATE NOT NULL
    )
    
    CREATE TABLE Colleges
    (
    ID INT IDENTITY NOT NULL,
    Name VARCHAR(255) NOT NULL,
    Abbreviation VARCHAR(255) NOT NULL
    )
    
    CREATE TABLE Courses
    (
    ID INT primary key  IDENTITY NOT NULL,
    College INT FOREIGN KEY REFERENCES Colleges(ID),
    Name VARCHAR(255) NOT NULL,
    CourseCode VARCHAR(255) NOT NULL
    )
    
    CREATE TABLE Subjects
    (
    ID INT IDENTITY NOT NULL,
    SubjectNumber VARCHAR(10),
    Description VARCHAR(255),
    Unit VARCHAR(255)
    )
    
    CREATE TABLE SchoolYears
    (
    ID INT IDENTITY NOT NULL,
    SchoolYear VARCHAR(255) NOT NULL
    )
    
    CREATE TABLE Semesters
    (
    ID INT IDENTITY NOT NULL,
    SchoolYear INT FOREIGN KEY REFERENCES SchoolYear(ID),
    Semester VARCHAR(255)
    )
    
    CREATE TABLE CourseSubjects
    (
    ID INT IDENTITY NOT NULL,
    Subject INT FOREIGN KEY REFERENCES Subjects(ID),
    Semester INT FOREIGN KEY REFERENCES Semesters(ID),
    Course INT FOREIGN KEY REFERENCES Courses(ID)
    )
    
    CREATE TABLE StudentSubjects
    (
    ID INT IDENTITY NOT NULL,
    Student INT FOREIGN KEY REFERENCES Students(ID),
    Subject INT FOREIGN KEY REFERENCES Subjects(ID),
    Semester INT FOREIGN KEY REFERENCES Semesters(ID)
    )
    Last edited by gvee; 12-05-13 at 11:07.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What class are you taking? Would help to know what level at which we are aiding.
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    “Before you can drink new tea, you must empty the cup of the old tea” --Zen proverb

    The IDENTITY table property is not a column, Since it is the physical insertion attempt count on one particular disk it never used. Wow! Look at the Kabbalah magic as it changes from a student to a college, to squids and automobiles.

    Do you really know anyone with a 255 character first name? The USPS uses 35 for an address line, based on a 3.5” label with 10 pitch type and five address lines max. If you had done research, you would knwo this.

    Length is one of the important constraints in RDBMS. That absurd 255 length is a great to destroy data integrity. You had an abbreviation of 255! LOL! Invite garbage data and it will come.

    You also missed the difference between a “birthday” and a “birth_date”

    CREATE TABLE Students
    (student_id CHAR(10) NOT NULL PRIMARY KEY,
    first_name VARCHAR(20) NOT NULL,
    middle_initial VARCHAR(1) DEFAULT ' ' NOT NULL
    CHECK (LEN(middle_initial) > 0),
    last_name VARCHAR(20) NOT NULL,
    birth_date DATE NOT NULL);

    I have forced the middle_initial to be at least one blank.

    Did you Google for college codes? Here is one source for the FICE codes:
    http://www.tsbpa.state.tx.us/pdffiles/x0011.pdf

    CREATE TABLE Colleges
    (fice CHAR(4) NOT NULL PRIMARY KEY
    college_name VARCHAR(35) NOT NULL);

    Courses today usually follow a standard encoding across schools. ACM, IEEE, etc have them defined very nicley.

    CREATE TABLE Courses
    (fice CHAR(4) NOT NULL
    REFERENCES Colleges(fice),
    course_code CHAR(5) NOT NULL,
    PRIMARY KEY (fice, course_code),
    course_name VARCHAR(25) NOT NULL);

    The Semester is a unit of temporal measurement, not an entity. Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

    CREATE TABLE Semesters
    (semester_name CHAR(10) NOT NULL PRIMARY KEY
    CHECK (semester_name LIKE '[12][0-9][0-9][0-9]S[1-3]'),
    semester_start_date DATE NOT NULL,
    semester_end_date DATE NOT NULL,
    CONSTRAINT date_ordering
    CHECK (semester_start_date <= semester_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. For your purposes, I did the same thing with a 'S' for semesters, but you can invent your own; there is no standard.

    I am going to stop now and let you look at this. “Before you can drink new tea, you must empty the cup of the old tea” --Zen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •