1 / 15

Database design

Database design. Using Access 2007. Access 2007. DMS Database management system Database Collection of data organized in a manner that allows access, retrieval, and use of that data. --- OR --- A place to store data and retrieve information. Access Objects….

devika
Download Presentation

Database design

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. Database design Using Access 2007

  2. Access 2007 • DMS • Database management system • Database • Collection of data organized in a manner that allows access, retrieval, and use of that data. --- OR --- • A place to store data and retrieve information

  3. Access Objects… • Tables • Like Excel • Enter, edit, and delete data • Display, sort and filter • Queries • Ask questions and get answers from data • Reports • Print customized displays of data • Forms • Visual • Can limit data by user

  4. Tables • Each row is a separate record, or an entry in the database • One customer • One order • Each record has a unique identifier, called a primary key, such as: • Customer ID • Student ID • SSN • Each column is a field • Name, address, phone number • Item number, quantity ordered Primary key field

  5. The key • To designing a good database …. A good plan

  6. Normalizing data • Table designs should eliminate redundant (repeating) data. This is called normalizing a database. • Customer names and addresses would be listed in a separate table, and not included with every order the customer makes • Students’ names and address are in a separate table, and not included with their schedule and grade information More info on database design for Access 2007 is available here.

  7. Steps for designing tables • Decide what tables to including in your database. An online clothing store might include: • Product inventory • Item number, Item name, purchase price, number in stock • Vendors (the people you buy from) • Vendor number, vendor name, address, city, state, zip, terms • Customers (the people you sell to) • Customer number, customer, name, address, city, state, zip, phone number • Orders • Customer number, item number, quantity

  8. Steps for designing tables • Choose a primary key for each table • Format the data for each field • Text or numbers • Maximum number of characters • Required or not? • Security – who will have access • Set relationships between tables

  9. Common Access Data types • Text fields (default) • May contain letters, numbers, and symbols • Maximum 255 characters • Number fields • May contains ONLY numbers • Can be positive or negative • Can be used for arithmetic operations • Currency fields • May contain ONLY monetary data • Will display like currency formatting in Excel • Can be used in arithmetic operations

  10. Number fields • Byte: Integer from 0 to 255 • Integer: Integers from -32,768 to 32,767 • Long Integer: Integers from -2,147,483,648 to 2,147,483,647. • Single: Numbers with up to 7 decimal places • Double: More decimal places; 2x the storage • Decimal: More decimal places; 3x the storage • Replication ID: Special identifier required for replication Choose the smallest size you can for your data

  11. Other Access Data types • Memo • Up to 63,999 characters • Date/Time • AutoNumber • Stores a unique sequential number for each record • Values CANNOT be re-used • Yes/No • Values can be Yes/No, True/False, or On/Off • OLE Object • Hyperlink • Attachment • Images, spreadsheets, etc.

  12. Relationships • Relationships exists between tables • One-to-many (most common) • Each customer has many orders • Each student has many classes • Each employee has many paychecks • One-to-one • Each customer has one address • Each student has one locker • Each employee works for one department • Many-to-many (least common)

  13. Facts about Access 2007 • Field names • Can have up to 64 characters • Can contain letters, numbers, characters and spaces EXCEPT • Periods (.) • Exclamation points (!) • Accent graves (`) • Square brackets ([ and ])

  14. Using Access • Some things about Access are different from other Office applications • All tables, queries, forms and reports are stored in the same file • When you start a new database, you have to name it before you can create any tables • Access saves database files with an .accdb extension • When you enter data, each record is automatically saved when you move to the next • If you delete a record or a table field, it gone for good.

  15. The Access Window

More Related