3.5: Solutions

3.1 Solution

CREATE TABLE Course (
    CourseId int PRIMARY KEY NOT NULL,
    Title varchar(50) NOT NULL,
    Credits int NOT NULL
);

CREATE TABLE Student (
    StudentId int PRIMARY KEY NOT NULL,
    LastName varchar(50) NOT NULL,
    FirstName varchar(25) NOT NULL,
    EnrollmentDate datetime NULL
);

CREATE TABLE Enrollment (
    EnrollmentId int PRIMARY KEY NOT NULL,
    CourseID int NOT NULL,
    StudentID int NOT NULL,
    Grade varchar(25) NULL,
    CONSTRAINT FK_Enrollment_Course FOREIGN KEY (CourseId)
        REFERENCES Course (CourseId),
    CONSTRAINT FK_Enrollment_Student FOREIGN KEY (StudentId)
        REFERENCES Student (StudentId)
);

3.2 Solution

CREATE TABLE Library (
    LibraryId int PRIMARY KEY NOT NULL,
    LocationName varchar(50) NOT NULL
);

CREATE TABLE Member (
    MemberId int PRIMARY KEY NOT NULL,
    FirstName varchar(25) NOT NULL,
    LastName varchar(25) NOT NULL,
    LibraryId int NOT NULL,
    CONSTRAINT FK_Member_Library FOREIGN KEY (LibraryId)
        REFERENCES Library (LibraryId)
);

CREATE TABLE Book (
    BookId int PRIMARY KEY NOT NULL,
    Title varchar(100) NOT NULL,
    Author varchar(50) NOT NULL,
    CheckOutDate datetime NULL,
    DueDate datetime NULL,
    MemberId int NULL,
    LibraryId int NOT NULL,
    CONSTRAINT FK_Book_Library FOREIGN KEY (LibraryId)
        REFERENCES Library (LibraryId),
    CONSTRAINT FK_Book_Member FOREIGN KEY (MemberId)
        REFERENCES Member (MemberId)
);

Here is the UML table diagram:

Solution 2

3.3 Solution

CREATE TABLE Contact (
    ContactId int PRIMARY KEY NOT NULL,
    Phone varchar(10) NOT NULL,
    HomeAddress varchar(100) NOT NULL,
    Email varchar(50) NULL
);

CREATE TABLE Staff (
    StaffId int PRIMARY KEY NOT NULL,
    StaffName varchar(50) NOT NULL,
    Position varchar(25) NOT NULL,
    Salary money NOT NULL,
    ContactId int NOT NULL,
    CONSTRAINT FK_Staff_Contact FOREIGN KEY (ContactId)
        REFERENCES Contact (ContactId)
);

CREATE TABLE Owner (
    OwnerId int PRIMARY KEY NOT NULL,
    OwnerName varchar(50) NOT NULL,
    ContactId int NOT NULL,
    PetId int NOT NULL,
    CONSTRAINT FK_Owner_Contact FOREIGN KEY (ContactId)
        REFERENCES Contact (ContactId)
);

CREATE TABLE Pet (
    PetId int PRIMARY KEY NOT NULL,
    PetName varchar(25) NOT NULL,
    AnimalType varchar(15) NOT NULL,
    Breed varchar(25) NOT NULL,
    Color varchar(25) NOT NULL,
    PetWeight int NULL,
    LastAppointment datetime NULL,
    ServiceId int NULL,
    OwnerId int NOT NULL
    CONSTRAINT FK_Pet_Owner FOREIGN KEY (OwnerId)
        REFERENCES Owner (OwnerId)
);

CREATE TABLE Service (
    ServiceId int PRIMARY KEY NOT NULL,
    ServiceName varchar(25) NOT NULL,
    Cost money NOT NULL,
    PetID int NULL,
    CONSTRAINT FK_Service_Pet FOREIGN KEY (PetId)
        REFERENCES Pet (PetId)
);