CS331 Assignment 3
.pdf
School
New Jersey Institute Of Technology**We aren't endorsed by this school
Course
CS 331
Subject
Information Systems
Date
Dec 16, 2024
Pages
2
Uploaded by BarristerTitanium15750
1.Discuss the entity integrity and referential integrity constraints. Why is eachConsidered important?a.Entity integrity ensures that each table has a primary key and that thevalues of the primary key are unique and not null. This is importantbecause it guarantees that each record can be uniquely identified in atable, preventing duplicate recordsb.Referential integrity ensures that a foreign key value either matches aprimary key value in another table or is null. This prevents orphan recordsand maintains the logical relationships between tables. It is important formaintaining data consistency and ensuring that relationships betweentables remain valid.2.List the data types that are allowed for SQL attributes.a.- `INT` - Integer data typeb.- `VARCHAR(n)` - Variable-length string with a maximum length of nc.- `CHAR(n)` - Fixed-length string with a length of nd.- `DATE` - Date data typee.- `FLOAT` - Floating-point numberf.- `BOOLEAN` - Boolean data type (true/false)g.- `TEXT` - Large text datah.- `DECIMAL(p, s)` - Fixed-point number with precision p and scales3.Write Insert UPDATE, DELETE SQL Data Manipulation Language statements forLIBRARY schemaInsert Statements:a.INSERT INTO PUBLISHER (Name, Address, Phone) VALUES (‘Sahil RandomHouse', '123 Publisher St', '123-456-7890');b.INSERT INTO BOOK (Book_id, Title, Publisher_name) VALUES (1, 'The GreatGatsby', Sahil Random House');c.INSERT INTO BORROWER (Card, Name, Address, Phone) VALUES (1, 'JohnSmith, '456 Addison Ave', ‘123-456-7890’');Update Statements:a.UPDATE BOOK SET Title = 'The Great Gatsby: Revised Edition' WHEREBook_id = 1;b.UPDATE BORROWER SET Phone = '111-222-3333' WHERE Card = 1;c.UPDATE PUBLISHER SET Address = '789 New Publisher Rd' WHERE Name =‘Sahil Random House';Delete Statement :a.DELETE FROM BORROWER WHERE Card = 1;b.DELETE FROM BOOK WHERE Book_id = 1;
c.DELETE FROM PUBLISHER WHERE Name = ‘Sahil Random House';4.Describe the four clauses in the syntax of a simple SQL retrieval query. Show what typeof constructs can be specified in each of the clauses. Which are required and whichareoptional?a.SELECT:-Specifies the columns to retrieve.-Required.b.FROM:- Specifies the tables from which to retrieve the data.- Required.c.WHERE:- Specifies the conditions that must be met for records to be included in the results.- Optional.d.ORDER BY:- Specifies the order in which to return the results.