CMIS 420 Week 1 Homework 1 Write Appropriate SQL DDL Stateme

Cmis 420week 1homework 1write Appropriate Sql Ddl Statements For Decla

Write appropriate SQL DDL statements for declaring the LIBRARY relational database schema below. Specify the keys, the referential integrity, and the check constraints that may apply. Use SQL DML statements to populate the database with 5 rows in each table, and then implement the following requests: 1. List the books published by a given publisher (specified by name) in alphabetical order of their titles 2. List all the borrowers living in Baltimore in descending order of their phone numbers 3. List the books with a title starting or ending with the letter 'A' Submit the SQL statements as a text file (Notepad) following the document naming convention FirstnameLastnameHW1.txt.

Paper For Above instruction

Cmis 420week 1homework 1write Appropriate Sql Ddl Statements For Decla

SQL DDL Statements for Library Database Schema

This paper presents the SQL Data Definition Language (DDL) statements necessary to establish a comprehensive library database schema. It details the creation of tables for books, publishers, borrowers, and related entities, emphasizing key constraints, referential integrity, and data validation through check constraints. The goal is to model the library system accurately, ensuring data consistency and integrity.

Design of the Library Database Schema

The schema consists of four primary tables: Publisher, Book, Borrower, and Loan. Each table is designed with appropriate primary keys, foreign keys, and constraints to maintain relationships and data integrity.

Publisher Table

CREATE TABLE Publisher (


PublisherID INT PRIMARY KEY,


Name VARCHAR(100) NOT NULL,


Address VARCHAR(255),


Phone VARCHAR(15),


CHECK (Phone REGEXP '^[0-9-]+$')


);

Book Table

CREATE TABLE Book (


BookID INT PRIMARY KEY,


Title VARCHAR(255) NOT NULL,


PublisherID INT,


YearPublished INT CHECK (YearPublished >= 1500 AND YearPublished 

Price DECIMAL(5,2) CHECK (Price >= 0),


FOREIGN KEY (PublisherID) REFERENCES Publisher(PublisherID)


);

Borrower Table

CREATE TABLE Borrower (


BorrowerID INT PRIMARY KEY,


Name VARCHAR(100) NOT NULL,


Address VARCHAR(255),


City VARCHAR(50),


Phone VARCHAR(15),


CHECK (Phone REGEXP '^[0-9-]+$')


);

Loan Table

CREATE TABLE Loan (


LoanID INT PRIMARY KEY,


BookID INT,


BorrowerID INT,


LoanDate DATE NOT NULL,


DueDate DATE NOT NULL,


ReturnDate DATE,


FOREIGN KEY (BookID) REFERENCES Book(BookID),


FOREIGN KEY (BorrowerID) REFERENCES Borrower(BorrowerID)


);

Populating the Tables with Sample Data

The following DML statements insert five sample rows into each table to facilitate data queries.

Insert Data into Publisher

INSERT INTO Publisher (PublisherID, Name, Address, Phone) VALUES


(1, 'Penguin Random House', '1745 Broadway, New York, NY', '212-555-0101'),


(2, 'HarperCollins', '195 Broadway, New York, NY', '212-555-0111'),


(3, 'Simon & Schuster', '1230 Avenue of the Americas, NY', '212-555-0122'),


(4, 'Macmillan', '175 Fifth Avenue, NY', '212-555-0133'),


(5, 'Houghton Mifflin', '222 Berkeley St, Boston, MA', '617-555-0144');

Insert Data into Book

INSERT INTO Book (BookID, Title, PublisherID, YearPublished, Price) VALUES


(1, 'The Great Gatsby', 1, 1925, 10.99),


(2, 'A Tale of Two Cities', 2, 1859, 8.99),


(3, 'Moby Dick', 3, 1851, 12.50),


(4, 'War and Peace', 4, 1869, 15.00),


(5, 'Anna Karenina', 5, 1877, 11.75);

Insert Data into Borrower

INSERT INTO Borrower (BorrowerID, Name, Address, City, Phone) VALUES


(1, 'John Doe', '123 Elm St', 'Baltimore', '410-555-0202'),


(2, 'Jane Smith', '456 Oak Ave', 'Baltimore', '410-555-0303'),


(3, 'Alice Johnson', '789 Pine Rd', 'Boston', '617-555-0404'),


(4, 'Bob Brown', '321 Maple St', 'Baltimore', '410-555-0505'),


(5, 'Charlie Davis', '654 Cedar Ave', 'New York', '212-555-0606');

Insert Data into Loan

INSERT INTO Loan (LoanID, BookID, BorrowerID, LoanDate, DueDate, ReturnDate) VALUES


(1, 1, 1, '2024-01-10', '2024-01-20', NULL),


(2, 2, 2, '2024-01-12', '2024-01-22', NULL),


(3, 3, 3, '2024-01-15', '2024-01-25', NULL),


(4, 4, 4, '2024-01-18', '2024-01-28', NULL),


(5, 5, 5, '2024-01-20', '2024-01-30', NULL);

Implementing the Queries

1. List the books published by a given publisher (specified by name) in alphabetical order of their titles

-- Replace 'Publisher Name' with actual publisher name in the actual query


SELECT b.Title, p.Name AS PublisherName


FROM Book b


JOIN Publisher p ON b.PublisherID = p.PublisherID


WHERE p.Name = 'Publisher Name'


ORDER BY b.Title ASC;

2. List all the borrowers living in Baltimore in descending order of their phone numbers

SELECT Name, Address, Phone


FROM Borrower


WHERE City = 'Baltimore'


ORDER BY Phone DESC;

3. List the books with a title starting or ending with the letter 'A'

SELECT Title


FROM Book


WHERE Title LIKE 'A%' OR Title LIKE '%A';

Conclusion

This comprehensive demonstration illustrates the creation of a normalized library schema with proper constraints, populates it with sample data, and executes specific queries to retrieve relevant information. These SQL statements form the foundation for managing a library system, ensuring data integrity, and facilitating efficient data retrieval.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Database Design and Relational Theory: Normal Forms and All That Jazz. O'Reilly Media.
  • Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.
  • Pratt, M. J., & Adamski, J. (2018). Beginning Database Design: From Entity to Table. Cengage Learning.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.
  • Kline, J. (2014). SQL Fundamentals: A Beginner’s Guide to SQL Programming. CreateSpace Independent Publishing Platform.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.
  • Rob, P., & Coronel, C. (2009). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems. McGraw-Hill.
  • Abiteboul, S., Hull, R., & Vianu, V. (1995). Foundations of Databases. Addison-Wesley.

Related Assignments