1 / 13

Creating a Database in Access

Creating a Database in Access. Creating a database involves Logical design of tables and relationships Physical design of tables and relationships Populating tables. Logical Design of Library DB. DB consists of these tables Books (bkID, bkTitle, bkPrice) Authors (auID, auName, auPhone)

burt
Download Presentation

Creating a Database in Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating a Database in Access • Creating a database involves • Logical design of tables and relationships • Physical design of tables and relationships • Populating tables

  2. Logical Design of Library DB • DB consists of these tables • Books (bkID, bkTitle, bkPrice) • Authors (auID, auName, auPhone) • Publishers (pubID, pubName, pubPhone)

  3. Logical Design of Library DBTable Relationships Authors auID auName auPhone BooksbkID bkTitle bkPrice pubID auID PublisherspubID pubName pubPhone

  4. Physical Design of DB (Access) • Create a new Database (e.g., myLibrary) • Create tables • Create fields • Go to design view • Insert fields • Select field types • Determine field properties • Create more tables and their fields

  5. Physical Design of DB (Access) • Relate tables • DesignRelationships • Connect key fields

  6. Input Table Data • Open a DB in Access (e.g., myLibrary) • Open a table (e.g., publishers) • Enter field data

  7. Queries • One table • Display publisher names and their phone numbers • Display book titles and their prices • Display author names and their phone numbers • Two tables • Display book titles, their prices, and their publishers • Display book titles, their prices, and author names • Three tables • Display book titles, their authors, and their publishers

  8. Queries • Which books are $25 or less? • What is the phone number of publisher Big House? • What is the phone number of Shakespeare? • Who publishes the book Hamlet? • All books published by Big House over $10.00 • All books written by Shakespeare and their publisher

  9. Additional Queries • List all copywritten materials used in Illiad. • List all special photographs used in Balloon. • Display the author, publisher, and ISBN of Emma, and owners of all copywritten material • What is the name of author Spencer’s wife? • How many children does author Shakespeare have?

  10. Modifying Database • The current design allows only 1 author for each book. • How can we allow more than 1 author for one book (e.g., co-authors)? Authors auID auName auPhone BooksbkID bkTitle bkPrice pubID auID PublisherspubID pubName pubPhone 1 1 ∞ ∞

  11. Modifying Database • Remember, every relationship between two tables must be 1-to-∞ or ∞-to-1. • Solution: Introduce an artificial linking table between Books a ∞ nd Authors. BooksAuthors bkauID bkID auID Authors auID auName auPhone PublisherspubID pubName pubPhone BooksbkID bkTitle bkPrice pubID auID 1 1 ∞ ∞

  12. Logical Design of Library DBTable Relationships Authors auID auName auPhone BooksbkID bkTitle bkPrice pubID auID PublisherspubID pubName pubPhone

  13. Access Exercises • Tables • Books • Authors • Publishers • BooksAuthors

More Related