Software Engineer, Machine Learning

Meta

Table: Sales +-------------+-------+ | Column Name | Type | +-------------+-------+ | sale_id | int | | product_id | int | | year | int | | quantity | int | | price | int | +-------------+-------+ (sale_id, year) is the primary key (combination of columns with unique values) of this table. product_id is a foreign key (reference column) to Product table. Each row of this table shows a sale on the product product_id in a certain year. Note that the price is per unit. Table: Product +--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | +--------------+---------+ product_id is the primary key (column with unique values) of this table. Each row of this table indicates the product name of each product. Write a solution to select the product id, year, quantity, and price for the first year of every product sold. Return the resulting table in any order. Example: Sales table: +---------+------------+------+----------+-------+ | sale_id | product_id | year | quantity | price | +---------+------------+------+----------+-------+ | 1 | 100 | 2008 | 10 | 5000 | | 2 | 100 | 2009 | 12 | 5000 | | 7 | 200 | 2011 | 15 | 9000 | Product table: +------------+--------------+ | product_id | product_name | +------------+--------------+ | 100 | Nokia | | 200 | Apple | | 300 | Samsung | Output: +------------+------------+----------+-------+ | product_id | first_year | quantity | price | +------------+------------+----------+-------+ | 100 | 2008 | 10 | 5000 | | 200 | 2011 | 15 | 9000 | `

Meta

Let's design a database for a library. Consider the following requirements: Books: The library has a collection of books. Each book has a title, an author (or authors), a publication year, an ISBN, and a genre. A book can have multiple authors. Members: The library has members who can borrow books. Each member has a unique member ID, a name, an address, and a phone number. Borrowing: The library needs to keep track of which books are borrowed by which members and when they are borrowed and returned. A member can borrow multiple books, and a book can be borrowed by multiple members over time. Reservations: Members can reserve books that are currently checked out. The system needs to keep track of reservations and their order. Late Fees: The library charges late fees for overdue books. The system needs to calculate and track these fees. Based on these requirements, design a relational database schema. Include the tables, columns, data types, primary keys, and foreign keys. Provide a brief explanation for each table and the relationships between them. How would you handle the many-to-many relationship between books and authors? How would you handle the many-to-many relationship between members and borrowed books? How would you ensure data integrity, such as preventing a member from borrowing the same book twice at the same time?

Meta

Where are you from in the US?