Consider the following schema:
Sellers(SellerId, SellerName, address, phone)
Pieces(PieceId, PieceName, color, type)
Inventory(SellerId, PieceId, price, location)
Express following queries in relational algebra:
1. Retrieve the names of sellers who sale the black pieces.
2. Retrieve the SellerId of sellers who sale the black pieces or
are at 19 Hussein Al Bouri Street.
3. Retrieve the sellersId of sellers who sale every piece.
4. Retrieve the SellersId of sellers who sale the white pieces and
the black pieces.
1) Retrieve the names of sellers who sale the black pieces.
SELECT s.SellerName
FROM Sellers s, Pieces P, Inventory I
WHERE s.SellerId = I.SellerId and I.PieceId = P.PieceId and P.color = "black";
2) Retrieve the SellerId of sellers who sale the black pieces or are at 19 Hussein Al Bouri Street
SELECT s.SelllerId
FROM Sellers s, Pieces P, Inventory I
WHERE s.SellerId = I.SellerId and I.PieceId = P.PieceId and ( P.color = "black" or S.address = "19 Hussein Al Bouri Street" );
3) Retrieve the sellersId of sellers who sale every piece.
SELECT s.SelllerId
FROM Sellers s, Pieces P, Inventory I
WHERE s.SellerId = I.SellerId and I.PieceId = P.PieceId;
4) Retrieve the SellersId of sellers who sale the white pieces and the black pieces.
SELECT s.SellerId
FROM Sellers s, Pieces P, Inventory I
WHERE s.SellerId = I.SellerId and I.PieceId = P.PieceId and ( P.color = "black" and P.color = "white" );
Consider the following schema: Sellers(SellerId, SellerName, address, phone) Pieces(PieceId, PieceName, color, type) Inventory(SellerId, PieceId, price, location)...
Please finish all parts, thanks!
2) Consider the following schema: Suppliers(sid: integer, sname: string, address: string) Parts(pid:integer, pname: string, color: string ) Catalog( sid: integer, pid: integer, cost: real) Write the following queries in relational algebra. a) Find the names of suppliers who supply some red part b) Find the sids of suppliers who supply some red or green part c) Find the sids of suppliers who supply some red and some green part. d) Find the sids of suppliers...
Consider the following GRADEBOOK relational schema describing
the data for a grade
book of a particular instructor (Note: The attributes A, B, C,
and D store grade cutoffs.)
catalog(cno,ctitle)
students(sid,fname,lname,minit)
courses(term,secno,cno,A,B,C,D)
enrolls(sid,term,secno)
Specify and execute the following queries using the RA
interpreter on the GRADEBOOK
database schema.
a. Retrieve the names of students enrolled in the ‘Automata’
class in the term of Fall
1996.
b. Retrieve the SID values of students who have enrolled in
CSc226 as well as CSc227....
C. Answer the following five (5) questions, based on the schema provided.Consider the following schema:Supplier (sid: integer, sname: string, address: string)Part(pid: integer, pname: string, , color: string)Catalog(sid: integer, pid: integer, cost: real)The relation Supplier stores suppliers and the primary key of that relation is sid. The relation Part stores parts, and pid is the primary key of that relation. Finally, Catalog stores which supplier supplies which part and at which cost (price). The primary key is the combination of the...
- Consider the following relational schema. - Write SQL statements for the following queries employee fname CHARACTER VARYING (15) minit CHARACTER VARYING(1) InameCHARACTER VARYING(15) essn CHARACTERO) bdate DATE address CHARACTER VARYING(50) dependent works on CHARACTERO dependent name CHARACTER VARYING(15) essn CHARACTER(9) pno NUMERIC hours NUMERIC CHARACTER() DATE CHARACTER VARYING(B) bdate CHARACTER(1) dept locations elationship salary NUMERIC super ssn CHARACTERO) dno dno NUMERIC dlocation CHARACTER VARYING(15) NUMERIC department CHARACTER VARYING(25) NUMERIC CHARACTERO) project dno mgssn mgstartdate DATE pname CHARACTER VARYING(25) pno...
Given the following relational database schema (primary keys are bold and underlined). Answer questions 2.1 to 2.4 Orders(orderld, customerld, dateOrdered, dateRequired, status) Customer(customerld, customerLastName, customerStreet, customerCity, customerState, customer Lip OrderDetails(orderld.productld, quantity, lineNumber, amount) Products(productld, name, description, quantity, unitPrice) Account(accountNumber, customerld, dateOpened, creditCard, mailingStreet, mailingCity, mailingState, mailingZip) 2.1 (2 Points) List all possible foreign keys. For each foreign key list both the referencing and referenced relations. 2.2 (2 Points) Devise a reasonable database instance by filling the tables with data of...
consider the following schema: Product(pid, name, type, manufacturer, price) key: pid Buys(cid, pid) key: cid and pid together Customer(cid, cname, age, gender) key: cid 1) write the following query in relational algebra; find the names of all customers who have purchased all products manufactured by sears. 2)write the following in sql: find the names of all the customers who have not purchased the most expensive product. 3)write the following query in sql: find the best selling products (in terms of...
consider the following schema: Product(pid, name, type, manufacturer, price) key: pid Buys(cid, pid) key: cid and pid together Customer(cid, cname, age, gender) key: cid 1) write the following query in relational algebra; find the names of all customers who have purchased all products manufactured by sears. 2)write the following in sql: find the names of all the customers who have not purchased the most expensive product. 3)write the following query in sql: find the best selling products (in terms of...
Consider the following schema: SUPPLIERS (SID: integer, SNAME: string, STREET: string, CITY: string, ZIP: string) PARTS (PID: integer, PNAME: string, COLOR: string) CATALOG (SID: integer, PID: integer, COST: real) The primary key attributes are underlined, and the domain of each attribute is listed after the attribute name. Thus, SID is the primary key for SUPPLIERS, PID is the primary key for PARTS, and SID and PID together form the primary key for CATALOG. Attribute SID in CATALOG is a foreign...
Consider a database with the following schema. LIKES(drinker,beer); /* key: all columns */ FREQUENTS(drinker,pub); /* key: all columns */ SERVES(pub,beer,cost); /* key: (pub,beer) */ Write the following queries in relational algebra. You can use the math notation (greek letters sigma, pi, etc.) or the ASCii "linear" notation I used in class. In order to make things more clear, please use intermediate results defined with the assignment notation in the algebra: R(a,b) := <rel-alg expression>. Try to give meaningful names for...
Consider the SOCIAL NETWORK relational database schema description provided below which is used to manage a social network, where: - Persons are the users - They can have friends (a friend relation is symmetric meaning that if person1 is friend of person2, the person2 is automatically friend of person1) - They can post multiple posts on his wall or in other friend’s wall - They can like posts with different types of likes: like, love, fun, wow, sad or angry....