Consider the following relational schema, representing five relations describing shopping transactions and information about credit cards...

Transcribed Image Text:

Consider the following relational schema, representing five relations describing shopping transactions and information about credit cards generating them [the used notation is explained above]. SHOPPINGTRANSACTION (Transld, Date, Amount, Currency, Exchange Rate, CardNbrCREDITCARD, StoreldSTORE) CREDITCARD (CardNbr, Card TypeCARDTYPE, CardOwnerOWNER, ExpDate, Limit) OWNER (Ownerld, Name, Surname, BirthDate, Job, IncomeLevel, Level) CARDTYPE (CardType, Circuit, Issuer, Address, Country, CardName, Color) STORE (Storeld, Name, Address, Country, Zone, Web, Email, Type) 1) Answer the following questions considering the domain described by the previous relational schema: 1.1. How many transactions can occur in each store? (provide the minimum and the maximum number) 1.2. How many credit cards with the same owner and the same type can exist? (provide the minimum and the maximum number) 2) Write the SQL query representing the following request: 2.1. Determine the number and the expire date of the credit cards with a limit equal to 2.000, that have not generated any transaction in 2022. Notation Underlined Superscript name of relation Example A or A, B A" or A", B Meaning The attribute(s) is (are) a primary key The attribute(s) is (are) a foreign key referencing relation R As an example, the schema R(A, B, C, D, ES) S(E, G, H) corresponds to the following SQL tables: CREATE TABLE R ( ); A <any SQL type>, B <any SQL type>, C <any SQL type>, D<any SQL type>, E <any SQL type>, PRIMARY KEY(A), FOREIGN KEY (E) REFERENCES S(F) CREATE TABLE S ( F <any SQL type>, G <any SQL type>, H <any SQL type>, PRIMARY KEY(F)) Consider the following relational schema, representing five relations describing shopping transactions and information about credit cards generating them [the used notation is explained above]. SHOPPINGTRANSACTION (Transld, Date, Amount, Currency, Exchange Rate, CardNbrCREDITCARD, StoreldSTORE) CREDITCARD (CardNbr, Card TypeCARDTYPE, CardOwnerOWNER, ExpDate, Limit) OWNER (Ownerld, Name, Surname, BirthDate, Job, IncomeLevel, Level) CARDTYPE (CardType, Circuit, Issuer, Address, Country, CardName, Color) STORE (Storeld, Name, Address, Country, Zone, Web, Email, Type) 1) Answer the following questions considering the domain described by the previous relational schema: 1.1. How many transactions can occur in each store? (provide the minimum and the maximum number) 1.2. How many credit cards with the same owner and the same type can exist? (provide the minimum and the maximum number) 2) Write the SQL query representing the following request: 2.1. Determine the number and the expire date of the credit cards with a limit equal to 2.000, that have not generated any transaction in 2022. Notation Underlined Superscript name of relation Example A or A, B A" or A", B Meaning The attribute(s) is (are) a primary key The attribute(s) is (are) a foreign key referencing relation R As an example, the schema R(A, B, C, D, ES) S(E, G, H) corresponds to the following SQL tables: CREATE TABLE R ( ); A <any SQL type>, B <any SQL type>, C <any SQL type>, D<any SQL type>, E <any SQL type>, PRIMARY KEY(A), FOREIGN KEY (E) REFERENCES S(F) CREATE TABLE S ( F <any SQL type>, G <any SQL type>, H <any SQL type>, PRIMARY KEY(F))