4.5: Solutions

4.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 timestamp NULL
);

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

4.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 REFERENCES Library (LibraryId)
);

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

Here is the UML table diagram:

Solution 2

4.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 REFERENCES Contact (ContactId)
);

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

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 timestamp NULL,
    ServiceId int NULL,
    OwnerId int REFERENCES Owner (OwnerId)
);

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