Technologies for Information Systems — Practice

Casabella (relational) rents vacation houses in Calabella; it takes over Casabuona (relational, Portobuono) to form Casaforse. Integrate the two relational DBs with minimal information loss (houses and owners are disjoint); then design a data warehouse over an extended Casabella schema to analyze the rents. Casabella: OWNER(OwnerSSN, Name, Surname, PhoneNumber, Gender); HOUSE(Address, Type, SquareMeters, DistanceFromSea, hasGarden, WeeklyPrice) // DistanceFromSea ∈ {"<30m","30-100m","100-500m",">500m"}; OWEDBY(OwnerSSN, HouseAddress, ProfitsPercentage). Casabuona: OWNER(OwnerID, "First name", "Last name", TelephoneNumber, SSN); ESTATE(HouseID, Address, isFlat, SquareMeters, DistanceFromSea, DailyPrice, OwnerID). Integration: 1 (4 pts) reverse-engineer each source to ER; 2 integrated schema — a. (2 pts) conflict table, b. (3 pts) integrated ER, c. (2 pts) logical translation; 3 (2 pts) GAV mappings for one table of your choice. DW (extended Casabella): GUEST(GuestSSN, Name, Surname, BirthDate, HomeCountry, RegistrationDate); COUNTRY(CountryName, Continent); RENT(GuestSSN, HouseAddress, StartDate, withPets). Casabella keeps 20% of each renting, 80% goes to the owners (split by percentage); all rents last one week. 4 (3 pts) extend the ER with the new tables; 5 for the rents fact — a. (3 pts) attribute tree with pruning/grafting, b. (3 pts) fact schema (queries given only to drive the design, no SQL needed).

List and describe the main dimensions of Data Quality.

Consider the following data mining problems: frequent itemset mining and association rule discovery. Define them, discuss the differences between the two problems, and provide an application example for each of them.

PoliCourses is an online platform offering courses and exercises. Each exercise has a maximum score; the score obtained is the percentage of completion applied to the maximum score, plus a possible bonus. A student can try each exercise only once. Design a data warehouse to analyze the exercise results. Operational database: - UNIVERSITY(UniversityName, Country, NumOfStudents, Rector) - STUDENT(StudentId, Name, Surname, BirthDate, Nationality, UniversityName*) // affiliation optional - COURSE(CourseId, CourseTitle, Area) - EXERCISE(ExerciseId, Title, Text, Type, Topic, MaximumScore, CourseId) - EXERCISERESULT(ExerciseId, StudentId, Date, PercentageOfCompletion, Bonus) 1. (3 pts) Reverse-engineer into an ER schema. 2. For the useful fact(s): a. (3 pts) attribute tree with pruning/grafting; b. (3 pts) fact schema; c. (2.5 pts) glossary. 3. (3 pts) Logical schema. 4. SQL: a. (2 pts) total solved exercises per university, exercise type and course area, with all one- and two-attribute aggregations; b. (2.5 pts) average bonus on Monday to students born in 1994 per course (id, title), topic and university country; c. (2 pts) total score by date, month and year including the single-level aggregations; d. (2 pts) the Italian student(s) with the greatest total score.

PoliCoach (Italy) and UniCoach (France, Spain, Portugal) run coach services and have merged into UniPoliCoach. Integrate the two relational databases into one relational DB losing the least possible information. People in the two sources are disjoint; 1 mile = 1.609 km. PoliCoach: VEHICLE(PlateNumber, Manufacturer, Displacement, NrSeats); TRAVEL(TravelId, Date, DepartureCity, DestinationCity, LengthMiles, VehiclePlateNumber); CUSTOMER(CustomerId, SSN, BirthDate, Name) // Name = "First Last"; TICKET(CustomerId, PurchaseTimestamp, Class, Price) // a ticket may cover several travels, price not decomposable; TICKETTRAVEL(CustomerId, PurchaseTimestamp, TravelId). UniCoach: COACH(PlateNumber, Brand, NrSeats); CITY(CityName, Country, NrInhabitants, Seaside); TRIP(TripId, Date, LengthKm, PricePerPerson, DepartureCity, DestinationCity, CoachPlateNumber); PASSENGER(SSN, BirthDate, GivenName, Surname, Address); TICKET(TicketId, PurchaseTimestamp, Class, TripId) // one trip per ticket, fixed per-person price; TICKETPASSENGER(TicketId, SSN). 1. (5 pts) Reverse-engineer each source to ER. 2. Integration — a. (3.5 pts) related-concept/conflict table; b. (4 pts) integrated ER; c. (2.5 pts) logical translation. 3. Query Q = "city and country of destination of first-class tickets for travels longer than 300 km": a. (1.5 pts) Q in SQL on the global schema; b. (4 pts) GAV mappings for the tables Q uses; c. (2.5 pts) rewriting of Q on the sources.

PoliScience sells and delivers scientific-paper downloads; every download is charged (re-downloads pay again). Design a data warehouse to analyze the downloads. Operational database: - COUNTRY(CountryName, Continent) - USER(UserId, GivenName, Surname, Affiliation*, CountryName) - PAPER(PaperId, Title, NumOfPages, PublicationDate, JournalName, Price) - DOWNLOAD(UserId, Date, Time, PaperId) - SUBTOPIC(SubtopicName, TopicName) - AUTHOR(AuthorId, GivenName, Surname, BirthYear, CountryName) - PAPERSUBTOPIC(PaperId, SubtopicName, Relevance) // relevances of a paper sum to 1 - PAPERAUTHOR(PaperId, AuthorId) 1. (3 pts) Reverse-engineer into an ER schema. 2. For the useful fact(s): a. (3 pts) attribute tree with pruning/grafting; b. (3 pts) fact schema; c. (2.5 pts) glossary. 3. (3 pts) Logical schema. 4. SQL: a. (2 pts) total income from European users by date, month and year including the single aggregations; b. (2 pts) downloads per subtopic of "Computer Science", weighted by relevance; c. (2 pts) for Asian authors, downloads per author, user affiliation and journal; d. (2.5 pts) total income by paper (id, title) and user country, for papers with at least one French author.

PoliPatents (America, relational) and UniPatents (Europe, one big XML document) both grant patents; each patent has one or more inventors. PoliPatents allows several assignees per patent; UniPatents allows one. They merged into UniPoliPatents — integrate the two sources into one relational DB with minimal information loss. Patents/assignees/inventors are disjoint; CPC and IPC category systems differ; UniPatents lets assignees/inventors change city per patent and stores inventor names as "First#Last". PoliPatents (relational): PATENT(PatentId, Title, GrantDate, Abstract, CPCCategory); CITY(CityName, Country); ASSIGNEE(AssigneeId, Name, CityName); INVENTOR(InventorId, Firstname, Lastname, CityName); PATENTASSIGNEE(PatentId, AssigneeId); PATENTINVENTOR(PatentId, InventorId); CITATION(CitingPatent, CitedPatent). UniPatents (XML/DTD): Patent(Title, Summary, GrantDate, Assignee, Inventors, IPCCategories) with per-patent Assignee{Name, CityName, Country} and repeated Inventor{Name, CityName, Country} and IPCCategory names. 1. (5 pts) Reverse-engineer each source to ER; for the XML source give also its relational translation. 2. Integration — a. (3.5 pts) conflict table; b. (4 pts) integrated ER; c. (2.5 pts) logical translation. 3. Query Q = "(patent id, assignee name) for assignees from Milan and patents granted in 2017": a. (1.5 pts) Q in SQL on the global schema; b. (4 pts) GAV mappings for the tables Q uses; c. (2.5 pts) rewriting of Q on the sources.

PoliBeach owns a chain of beach resorts selling rental packages (each package has a daily price and is bought for a number of days; the resort director may apply a per-sale discount). Design a data warehouse to analyze the package sales. Operational database: - CITY(CityName, Country) - BEACHRESORT(BeachResortId, BeachResortName, Director, CityName) - PACKAGE(PackageId, Description, Category, DailyPrice) - AVAILABLERENTALOBJECT(ObjectName, Description) - PACKAGECOMPOSITION(PackageId, RentalObjectName, Quantity) - CUSTOMER(CustomerId, Surname, GivenName, BirthDate, CityOfResidence) - SALE(CustomerId, PackageId, BeachResortId, StartDate, Duration, DiscountPercentage) 1. (3 pts) Reverse-engineer into an ER schema. 2. For the useful fact(s): a. (3 pts) attribute tree with pruning/grafting; b. (3 pts) fact schema; c. (2.5 pts) glossary. 3. (3 pts) Logical schema. 4. SQL: a. (2 pts) total sold packages per package category, resort director and customer city, with all one- and two-attribute aggregations; b. (2.5 pts) average duration for Italian customers born in 1989 per beach-resort city and package id; c. (2 pts) total income by start date, month and year including the single aggregations; d. (2 pts) per country, the beach resort(s) with the greatest income.

Describe the dimensional fact model used in the data warehouse context and define its main elements. Provide a small example.

Describe the GAV (global as view) and LAV (local as view) approaches used to define the mapping between the global logical schema and the single source schemata in the data integration context. Discuss the main differences between the two approaches and describe under which conditions GAV is more appropriate than LAV, and vice versa.

Describe the problem of personalization and list the various kinds of personalization with a small explanation of each.

Describe the main differences between materialized and virtual data integration, explaining which of these two approaches is used in the case of Data Warehousing and why.

Describe flow, level, and unitary measures in the data warehouse context. Provide a set of examples.

PoliRestaurants (Lombardy, Italian cuisine, dishes + beverages, home delivery available in some cities) and UniRestaurants (Piedmont/Liguria, Chinese/Japanese cuisine, no beverages, per-restaurant home delivery, courses with ingredients) have merged into UniPoliRestaurants. Integrate the two relational databases losing as little information as possible. PoliRestaurants: - CITY(CityName, HomeDeliveryService) - MANAGER(ManagerSSN, Surname, GivenName, PhoneNumber) - RESTAURANT(RestaurantCode, Name, Address, CityName, ManagerSSN) - CUSTOMER(CustomerSSN, Surname, GivenName, BirthDate) - ORDER(OrderCode, Timestamp, OrderType, ServiceDate, ServiceTime, DeliveryAddress*, RestaurantCode, CustomerSSN) - DISH(DishCode, Name, Description, Cost, IsVegan, WeightGrams) - BEVERAGE(BeverageCode, Name, Description, Price, IsAlcoholic) - ORDEREDDISH(OrderCode, DishCode, Quantity), ORDEREDBEVERAGE(OrderCode, BeverageCode, Quantity) UniRestaurants: - CITY(CityName, Region) - RESTAURANT(RestaurantCode, Name, Address, HomeDeliveryService, ManagerPhoneContact, CityName) - CUSTOMER(CustomerRegistrationNr, CustomerSSN, LastName, FirstName, PhoneNumber) - COURSE(CourseCode, Name, Description, CuisineType, Price, WeightKilos) - INGREDIENT(IngredientName, Category, Supplier), COURSEINGREDIENT(CourseCode, IngredientName, Quantity) - BOOKING(Timestamp, CustomerRegistrationNr, BookingType, ServiceDate, ServiceTime, DeliveryAddress*, CourseCode, RestaurantCode, Quantity) Customers and dishes of the two sources are disjoint. 1. (5 pts) Reverse-engineer each source into an ER schema. 2. Integration — a. (3.5 pts) conflict table; b. (3.5 pts) integrated ER schema; c. (2 pts) logical translation. 3. Query Q — "timestamp and customer SSN of take-away orders to restaurants in Milan or Turin involving at least one dish with weight > 200 g and price > 20 €": a. (1.5 pts) Q in SQL on the global schema; b. (4 pts) GAV mappings for the tables Q uses; c. (2.5 pts) rewriting of Q on the sources.

PoliAirways (direct flights across Europe) collects luggage-claim data from airports and completes it with the reimbursements paid to passengers (for irretrievably lost items and for delay expenses). Design a data warehouse to analyze the claims. Operational database: - CITY(CityName, Country) - AIRPORT(AirportCode, AirportName, CityName) - FLIGHT(FlightCode, DepartTime, ArrivTime, DepartAirportCode, ArrivAirportCode) - TICKET(TicketNr, FlightCode, FlightDate) - LUGGAGEITEM(ItemCode, Type, Weight, TicketNr) — Type ∈ {Trolley, Suitcase, Bag, Other}; Weight is discrete: Light/Medium/Large/ExtraLarge - CLAIM(ItemCode, ClaimDate, ClaimIssuer, IssuerPhoneNr, IssuerAddress, FoundDate*, PaidReimbursement) — FoundDate is null when the item is irretrievably lost 1. (3 pts) Reverse-engineer the logical schema into an ER conceptual schema. 2. For the useful fact(s): a. (3 pts) attribute tree with pruning/grafting; b. (3 pts) fact schema; c. (2 pts) glossary. 3. (3 pts) Logical schema. 4. SQL: a. (2 pts) for flights landing in Italy and items eventually found — average days to retrieve the item by arrival airport (code and name), date, weight and type; b. (2 pts) for flight YZ1234 — total reimbursement by month, quarter and year, including the aggregations by each attribute alone; c. (2 pts) total claims per day of week, departure city and arrival city, including all one/two-attribute sub-aggregations; d. (2 pts) per country, name and code of the departure airport(s) with the most claims.

PoliMultiplex is an Italian multiplex chain; every ticket is tied to the fidelity-card customer who bought it. The operational DB stores theaters, showing rooms, showings (each with its own price), movies and their main actors. Design a data warehouse to analyze the issued tickets. Operational database: - CITY(CityName, Region) - CUSTOMER(CustomerId, Name, HomeCityName, BirthYear) - THEATER(TheaterId, TheaterName, CityName) - SHOWINGROOM(TheaterId, RoomNr, NrSeats) - MOVIE(MovieId, Title, Genre, DurationInMinutes, ProductionYear) - SHOWING(ShowingId, Date, Time, TheaterId, RoomNr, MovieId, Price) - ACTOR(ActorId, ActorName, Gender, BirthYear, HomeCountry) - STARRING(MovieId, ActorId), TICKET(CustomerId, ShowingId) 1. (3 pts) Reverse-engineer the logical schema into an ER conceptual schema. 2. For the useful fact(s): a. (3 pts) attribute tree with pruning/grafting; b. (3 pts) fact schema; c. (2 pts) glossary. 3. (3 pts) Logical schema. 4. SQL: a. (2 pts) for customers from Rome — total income by date, month and year, including the aggregations by each attribute alone; b. (2 pts) total tickets per actor (id and name), customer home region, customer birth year and theater region; c. (2 pts) total income per movie (id and title), only for movies starring at least one actress; d. (2 pts) for Milan theaters — per day of week and theater (id and name), the genre(s) with the greatest number of tickets.

PoliTwitter stores European users' tweets in a relational DB; UniTwitter stores American users' tweets as JSON documents in a NoSQL store. UniPoliTwitter buys access to both, which are regularly updated, and wants a **virtual** integration system with a relational global schema. PoliTwitter (relational): - CITY(CityName, Country) - USER(Nickname, RealName, BirthDate*, CityName, ImageURL*, ProfileBackgroundColor, ProfileTextColor) - FOLLOWS(FollowerNickname, FolloweeNickname, StartDate) - TWEET(UserNickname, Timestamp, Text, CityName*, Language*, UserRetweetedTweet*, TimestampRetweetedTweet*) UniTwitter (JSON collection Tweet): documents with TweetId, Timestamp, Language?, Location? {Latitude, Longitude, City{Name, Country}}, Text, User{UserId, UserName, Real Name, City{Name, Country}}, RetweetOf?, FavoredBy?[user ids]. Users and tweets of the two sources are disjoint. 1. (5 pts) Reverse-engineer each source to ER; for the NoSQL source also give its relational translation. 2. Integration — a. (3.5 pts) conflict table; b. (3.5 pts) integrated ER schema; c. (2 pts) logical translation. 3. Query Q — "the nationalities of users who posted from Milan or New York at least one tweet containing the word 'soccer'": a. (1.5 pts) Q in SQL on the global schema; b. (4 pts) GAV mappings for the tables Q uses; c. (2.5 pts) rewriting of Q on the sources.

PoliConferences organizes scientific conferences grouped in series (one conference per year per series). Users register to a conference by choosing a registration category (each with its own price) and pay a price minus a per-registration discount. Design a data warehouse to analyze the registrations. Operational database: - COUNTRY(CountryName, Continent) - CONFERENCE(Series, Year, Country, StartDate, DurationInDays, Capacity) - USER(UserCode, Name, Surname, CountryName) - REGISTRATIONCATEGORY(Series, Year, CategoryName, Price) - REGISTRATION(UserCode, Series, Year, CategoryName, RegistrationDate, PercDiscount) 1. (3 pts) Reverse-engineer the logical schema into an ER conceptual schema. 2. For the useful fact(s): a. (3 pts) attribute tree with pruning/grafting; b. (3 pts) fact schema; c. (2 pts) glossary. 3. (3 pts) Logical schema. 4. SQL: a. (2 pts) considering only conferences in Europe, average discount per user continent and day of week; b. (2 pts) total revenue per conference country, registration date and user country, including all one- and two-attribute aggregations; c. (2 pts) per series, the conference(s) with the greatest number of registrants; d. (2 pts) conferences whose October 2016 revenue exceeded September 2016 revenue by more than 20%.

Describe concisely the concept of mashup and its utility in data integration, highlighting its distinctive features with respect to using other integration techniques.

Define what a Data Mart is in a Data Warehouse and clearly summarize the methodological steps that lead from a collection of datasets to the specification of the logical schemas of one or more Data Marts.

Define the typical operations necessary in the multidimensional data model that is at the basis of data warehouses.

Consider the following data mining problem: Sequential Pattern Discovery. Define and illustrate it clearly with an application example.

Define Wrappers and Mediators, explain in which circumstances their use is advised in Data Integration and the way they work. Discuss the various types of Wrappers and Mediators that have been introduced during the course.

Briefly define pervasive data management and the main problems that must be solved.

Define the concept of association rule in the data mining context, and give a small example.

Consider these data mining problems: classification and clustering. Define them, discuss the differences between the two problems, and provide an application example for each.

Define the concepts of valid time and transaction time in temporal databases, also describing their advantages and disadvantages.

Describe the Box-Plot method for displaying the distribution of data, using an example to illustrate it clearly.

PoliCo is a big company with branches in various countries, whose employees often travel for business. Each business trip is performed by an employee and is associated with an expenditure item; the trip must be authorized by the employee responsible for that expenditure item. Management asks you to design a data warehouse to analyze business trips. Operational database: - BUSINESSTRIP(SSNEmployee, StartingDate, DurationInDays, FoodCost, LodgingCost, TransportationCost, DestinationCity, ExpenditureItemCode) - EMPLOYEE(SSN, Surname, GivenName, HomeCity, Role, BranchName) — Role ∈ {Clerk, Manager, Technician} - CITY(CityName, Country) - EXPENDITUREITEM(ExpenditureItemCode, Name, Supervisor, YearlyAvailableBudget) - COMPANYBRANCH(BranchName, BranchCity) 1. (3 pts) Reverse-engineer the logical schema into an ER conceptual schema. 2. Discover the fact(s) useful for the queries below; for each fact: a. (3 pts) produce the attribute tree (with pruning and grafting); b. (3 pts) produce the fact schema; c. (2 pts) identify the measures and produce the glossary. 3. (3 pts) Produce a logical schema consistent with the conceptual schema. 4. Write in SQL, against your logical schema: a. (1.5 pts) average trip duration of technicians, by departure month and branch city; b. (1.5 pts) for Italian branches only, total expense per starting date, branch and employee home country — including all sub-aggregations on one or two of the three attributes; c. (2.5 pts) SSN and name of the supervisor(s) authorizing the most trips departing in Q2 2015 performed by employees residing in Milan; d. (2.5 pts) per expenditure item, the Italian destination city/cities with the most trips departing in 2014.

PoliHighways manages highways with a closed toll system (entrance + exit tollbooth, price by covered distance, prepaid contract with an electronic device per customer usable on vehicles of one category). UniRoads manages both highways and free roads (funded by a public body) with an open toll system (fixed price per crossed tollbooth, no contracts). The merged company UniPoliRoads asks you to integrate the two databases losing as little information as possible. PoliHighways: - HIGHWAY(HighwayCode, City1, City2) - TOLLBOOTH(TollboothCode, HighwayCode, City) - CONTRACT(DeviceNumber, PurchaseDate, CustomerSSN, VehicleCategory) - CUSTOMER(SSN, GivenName, Surname, BirthDate, HomeCity) - PAIDTOLL(DeviceNumber, Date, Time, PlateNumber, EntranceTollbooth, ExitTollbooth, PaidPrice) - MAINTENANCEWORK(HighwayCode, Date, KmPosition, Cost, CompanyName) UniRoads: - ROAD(RoadCode, Type, StartCity, EndCity, FundingBody*) - FUNDINGBODY(Name, Type, ContactPerson) - TOLLBOOTH(TollboothNumber, RoadCode, City, Price) - PAIDTOLL(Date, Time, PlateNumber, TollboothNumber, RoadCode) - MAINTENANCEWORK(RoadCode, Date, KmPosition, Cost, CompanyName) - MAINTENANCECOMPANY(CompanyName, Address, City, Phone) 1. (5 pts) Reverse-engineer each source into an ER conceptual schema. 2. Schema integration: a. (3.5 pts) related-concept identification and conflict analysis/resolution table (columns — PoliHighways concept, UniRoads concept, Conflict, Solution); b. (3.5 pts) integrated global conceptual schema (ER); c. (2 pts) conceptual-to-logical translation. 3. Query Q — "date, time and plate of toll payments at tollbooths in Milan on roads of type Highway with at least one maintenance work in 2015; for closed-system roads use entrance tollbooths only": a. (1.5 pts) write Q on the global schema (SQL or Datalog); b. (4 pts) write the GAV mappings for the tables Q uses; c. (2.5 pts) rewrite Q over the two sources.

PoliTours (Lombardy) stores its city tours as XML documents (one per city) served to a mobile app; UniTours (Piedmont) runs a relational database and its tours may also visit local food companies. The merged UniPoliTours wants one integrated relational database capturing all data from both sources, losing as little information as possible. PoliTours DTD (excerpt): City(Name, Province) contains Tour+(Number unique per city, Guide as "Firstname%Lastname", DurationInHours, Price) and a ReferenceAgency(Name, Address, City, OpeningHours?); each Tour contains PlaceOfInterest+(Name) each with Landmark+(Name, Description, Type). Names of cities, landmarks, places and agencies are unique. UniTours relational schema: - CITY(CityName, Province, NumberOfInhabitants) - POINTOFINTEREST(POIName, Description, Type, HistoricalPeriod*, CityName) - FOODCOMPANY(VATNumber, Name, Address, Phone, Email, CompanyType, CityName) - PRODUCT(ProductName, Year*, Description, FoodCompanyVATNumber) - GUIDE(SSN, Firstname, Lastname, Address, BirthDate) - TOUR(TourName, SSNGuide, DurationInMinutes, Cost) - POINTOFINTERESTTOUR(POIName, TourName), FOODCOMPANYTOUR(FoodCompanyVATNumber, TourName) 1. (5 pts) Reverse-engineer each source to ER; for the XML source also give its relational translation. 2. Schema integration — a. (3.5 pts) conflict table; b. (3.5 pts) integrated ER schema; c. (2 pts) logical translation. 3. Query Q — "firstname, lastname and (when available) birth date of guides supervising tours with at least one theater visit": a. (1.5 pts) Q on the global schema; b. (4 pts) GAV mappings for the tables Q uses; c. (2.5 pts) rewriting of Q on the sources.

Polimusic.com sells per-listening access to songs: each song has a price; 60% of each listening's income stays with the site and 40% is split among the featuring artists according to per-song weights (summing to 1). Design a data warehouse to analyze the listenings. Operational database: - ARTIST(ArtistCode, Name, RecordLabel) — an artist may be a person or a band - SONG(SongCode, Title, Genre, Language, DurationInSeconds, Price) - SONGARTIST(SongCode, ArtistCode, WeightForProfits) — weights per song sum to 1 - USER(UserCode, Surname, GivenName, BirthDate, HomeCountry, RegistrationDate) - COUNTRY(CountryName, Continent) - LISTENING(UserCode, SongCode, StartDate, StartTime, HighResolution) 1. (3 pts) Reverse-engineer the logical schema into an ER conceptual schema. 2. For the fact(s) useful for the queries below: a. (3 pts) attribute tree with pruning and grafting; b. (3 pts) fact schema; c. (2 pts) measures + glossary. 3. (3 pts) Logical schema consistent with the conceptual one. 4. SQL against your logical schema: a. (1.5 pts) for Sunday listenings by users born in 1990 — total site income (excluding the artist quota) by month, user, high-resolution flag and genre; b. (1.5 pts) per artist, name and number of listenings on 3 July 2014; c. (2.5 pts) the record label whose artists earned the greatest total income in 2015; d. (2.5 pts) per continent, code and title of the song with the greatest total listening time (all listenings count as complete).

PoliRestaurants sells home-delivery meals in Italy through its website. Each order concerns one dish and one chosen restaurant; each dish has a full price but every order carries its own discount (a percentage in [0,1]). Design a data warehouse to analyze the orders. Operational database: - RESTAURANT(RestaurantName, Address, CityName, SupervisorSSN, NumberOfEmployees) - SUPERVISOR(SupervisorSSN, Surname, GivenName) - CITY(CityName, Region) - DISHSUBCATEGORY(Subcategory, Category) — categories 'Food'/'Beverage' - DISH(DishName, Description, Subcategory, Price) - CUSTOMER(CustomerId, Surname, GivenName, BirthYear) - ORDER(CustomerId, Date, Time, DishName, RestaurantName, Discount, DeliveryAddress, DeliveryCity) 1. (3 pts) Reverse-engineer the logical schema into an ER conceptual schema. 2. For the useful fact(s): a. (3 pts) attribute tree with pruning/grafting; b. (3 pts) fact schema; c. (2 pts) measures + glossary. 3. (3 pts) Logical schema. 4. SQL: a. (2 pts) for category 'Food' — average discount by customer birth year, dish subcategory and restaurant city; b. (2 pts) for restaurants in Lombardy — total orders by restaurant, delivery city and date, including all one/two-attribute sub-aggregations; c. (2 pts) SSN and name of supervisors whose income grew ≥30% from 2014 to 2015; d. (2 pts) per delivery region, the dish(es) with the greatest number of orders.