Transcript nov17
CS 111 – Nov. 17 • Enterprise databases – Goals – Examples – Many-to-many relationship – Restricting one’s view of entire database – Design considerations – Concurrency • Commitment – Homework #2 due Nov. 29 Goals • • • • Retain and please customers Attention to detail Share data across company Allow employees to “view” the portion of the database relevant to them • Quick response to employee or customer query – Customers can interact with database via Web site – Also: allow concurrent access • Distributed database – Client capability at point of sale – Server capability at HQ – Don’t keep all data in one place: allow for physical partition or replication Examples • Luxury hotel – Goal: Personalized attention to detail – Store customer preferences for repeat visits – When cleaning room, take note of pillows used, newspapers, drink bottles and snack wrappers discarded – Log requests, compliments, complaints – On next visit, provide complimentary item as token of recognition • Garage – Take note of odometer reading – Do they only come in for oil change? Give birthday discount on other service Not 1 database • A large company probably needs more than 1 database, each with its own purpose – – – – Inventory control (materials, parts, finished goods) Sales (customers and orders) Manufacturing (production schedule, which factory) Accounting (corporate taxes, dividends, payroll) • Within a database, could have several tables: • Example: Customer, Orders, Order Details, Product – Many-to-many relationship Views • view = subset of a database – Details not relevant to you are hidden • When you access a database, it is for a particular function (data entry, lookup). Each function has its own view. • Some information is confidential • Ex. Customer order history – Contact info not needed • Ex. Item order history – Customer info, quantity of item on hand not needed • This is why many queries are not “select * …” Database design factors • Storage cost, Also the need for backups (reliability) • Processing cost: Do we have a powerful enough server? • Communication cost: Bandwidth and ISP server cost • Retrieval and processing: how long should it take to get a confirmation – Making all information available to everyone in company may be impractical. – Find out what info people need most often • Frequency of updates and queries – How often the store should send records to HQ Concurrency • Allowing several people to access database at same time • Example: – – – – Suppose there’s 1 seat left on tomorrow’s flight 17. Customers A and B simultaneously query availability for that flight. A buys a ticket. B’s screen still shows 1 seat available. B also buys a ticket. • Problem is that the purchase is not instantaneous, and 2 transactions can overlap. • Possible solution: record locking – Each seat on flight is a record in some table. – B cannot purchase seat, because A has already begun process of selecting and buying ticket.