Database代写：CS326 MySQL Scripts

发表于   |   分类于

代写MySQL的数据库建表题，按照类型和约束，建好对应的表即可。

Requirement

Since I will try to run your queries, please don’t include your schema name or user in the queries. I will use a USE SCHEMA statement before I run your queries.

video.sql

You have been hired by a video store to create a database for tracking DVDs and Blu-ray Disc (BD), customers, and who rented what. The database includes these relations: RentalItem, Customer, and Rentals. Use SQL DDL to specify the schema for this database, including all the applicable constraints. You are free to choose reasonable attributes for the first two relations. The relation Rentals is intended to describe who rented what and should have these attributes: CustomerId, ItemId, RentedFrom, RentedUntil, and DateReturned.

real_estate.sql

You are in a real estate business renting apartments to customers. Your job is to define an appropriate schema using SQL DDL. The relations are

  • Property(Id, Address, NumberOfUnits),
  • Unit(ApartmentNumber, PropertyId, RentalPrice, Size),
  • Customer (choose appropriate attributes),
  • Rentals (choose attributes; this relation should describe who rents what, since when, and until when), and
  • Payments (should describe who paid for which unit, how much, and when). Assume that a customer can rent more than one unit (in the same or different properties) and that the same unit can be co-rented by several customers.

movies.sql

You love movies and decided to create a personal database to help you with trivia questions. You chose to have the following relations: Actor, Studio, Movie, and PlayedIn (which actor played in which movie). The attributes of Movie are Name, Year, Studio, and Budget. The attributes of PlayedIn are Movie and Actor. You are free to choose the attributes for the other relations as appropriate. Use SQL DDL to design the schema and all the applicable constraints.

eBay.sql

You want to get rich by operating an auction Web site, similar to eBay, at which students can register used textbooks that they want to sell and other students can bid on purchasing those books. The site is to use the same proxy bidding system used by eBay(http://www.ebay.com).
Design a schema for the database required for the site. In the initial version of the system, the database must contain the following information:

  1. For each book being auctioned: name, authors, edition, ISBN number, bookId (unique), condition, initial offering price, current bid, current maximum bid, auction start date and time, auction end date and time, userId of the seller, userId of the current high bidder, and an indication that the auction is either currently active or complete.
  2. For each registered user: name, userId (unique), password, and e-mail address

room_scheduling.sql

You want to design a room-scheduling system that can be used by the faculty and staff of your department to schedule rooms for events, meetings, classes, etc. Design a schema for the database required for the system. The database must contain the following information:

  1. For each registered user: name, userId (unique), password, and e-mail address
  2. For each room: room number, start date of the event, start time of the event, duration of the event, repetition of the event (once, daily, weekly, monthly,mon-wed-fri, or tues-thurs), and end date of repetitive event.

library.sql

Design the schema for a library system. The following data should either be contained directly in the system or it should be possible to calculate it from stored information:

  1. About each patron: name, password, address, Id, unpaid fines, identity of each book the patron has currently withdrawn, and each book’s due date
  2. About each book: ISBN number, title, author(s), year of publication, shelfId, publisher, and status (on-shelf, on-loan, on-hold, or on-loan-and-on-hold). For books on-loan the database shall contain the Id of the patron involved and the due date. For books on hold the database shall contain a list of Ids of patrons who have requested the book.
  3. About each shelf: shelfId and capacity (in number of books)
  4. About each author: year of birth

The system should enforce the following integrity constraints. You should decide whether a particular constraint will be embedded in the schema, and, if so, show how this is done or will be enforced in the code of a transaction.

  1. The number of books on a shelf cannot exceed its capacity.
  2. A patron cannot withdraw more than two books at a time.
  3. A patron cannot withdraw a book if his/her unpaid fines exceed $5. Assume that a book becomes overdue after two weeks and that it accumulates a fine at the rate of 10c a day.