Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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 ... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamentals of Database Systems

Authors: Ramez Elmasri, Shamkant Navathe

6th edition

136086209, 978-0136086208

More Books

Students also viewed these Computer Engineering questions