Question
Consider the following Book Rental database schema: Student (SID, SName, SMobile, Collage, Section, Level, Address, Email) Loan (SID, ISBN, LDate, Lduration, Returned) Book (ISBN, BName,
Consider the following Book Rental database schema:
Student (SID, SName, SMobile, Collage, Section, Level, Address, Email)
Loan (SID, ISBN, LDate, Lduration, Returned)
Book (ISBN, BName, Topic, Avalible_ copy, Type, ASSN)
Author (SSN, AName, AAddress)
Give the SQL-DDL definitions for these database objects, use appropriate data types and include the following integrity constraints in the DDL definition:
Identify and include referential integrity constraints that should hold in this database.
Student Name, Mobile, college and section is a mandatory field, and student default address is "Riyadh, KSU" Book Type should be one of the following: book, journal or research. Topic is a mandatory field.
The Loan duration should not exceed 14 day.
A student cannot be deleted if he/she borrowed a book.
An author cannot be deleted if he wrote a book.
If we delete/update a book then we have to remove/update all loans belonging to it.
NOTE: Since the data type of the FK must match the data type of PK in which they reference to, thus it would be better if we define a domain to hold the data type of both of them and any additional constraints shared between them to avoid errors.
Step by Step Solution
3.48 Rating (155 Votes )
There are 3 Steps involved in it
Step: 1
Query SELECT COUNT AS Total students accepted in Year 2020 FROM student WHERE UniversityIntranceYear ...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started