# 4.5: Solutions

## 4.1 Solution

```sql
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

```sql
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](/files/-LAU8o4pExXW-YIv0Idw)

## 4.3 Solution

```sql
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        
);
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://eleven-fifty-academy.gitbook.io/sql-101-basics/part-4-postgresql-challenges/4.5-solutions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.