database design using microsoft access. what is a database? dms database management system ...
TRANSCRIPT
![Page 1: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/1.jpg)
DATABASE D
ESIGN
USING M
ICROSOFT
ACCESS
![Page 2: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/2.jpg)
What is a Database?
DMSDatabase management system
DatabaseCollection of data organized in a manner that allows access, retrieval, and use of that data. --- OR ---A place to store data and retrieve information
![Page 3: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/3.jpg)
DATABASE D
ESIGN
USING A
CCESS 2007
![Page 4: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/4.jpg)
ACCESS OBJECTS…
![Page 5: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/5.jpg)
Each row is a separate record, or an entry in the database
One customerOne order
Each record has a unique identifier, called a primary key, such as:
Customer IDStudent IDSSN
Each column is a fieldName, address, phone numberItem number, quantity ordered
TABLES
![Page 6: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/6.jpg)
THE KEY
To designing a good database ….
A good plan
![Page 7: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/7.jpg)
NORMALIZING DATA
Table designs should eliminate redundant (repeating) data. This is called normalizing a database.
Customer names and addresses should 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.
![Page 8: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/8.jpg)
STEPS FOR DESIGNING TABLES
What tables? 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
OrdersCustomer number, item number, quantity
![Page 9: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/9.jpg)
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
![Page 10: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/10.jpg)
COMMON ACCESS DATA TYPES
![Page 11: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/11.jpg)
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 storageDecimal:
More decimal places; 3x the storage Replication ID: Special identifier required for
replication
Choose the smallest size you can for your data
![Page 12: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/12.jpg)
OTHER ACCESS DATA TYPES Memo
Up to 63,999 characters Date/Time AutoNumber
Stores a unique sequential number for each recordValues CANNOT be re-used
Yes/NoValues can be Yes/No, True/False, or On/Off
OLE Object Hyperlink Attachment
Images, spreadsheets, etc.
![Page 13: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/13.jpg)
RELATIONSHIPS
Relationships exists between tables One-to-many (most common)
Each customer has many ordersEach student has many classesEach employee has many paychecks
One-to-oneEach customer has one addressEach student has one lockerEach employee works for one department
Many-to-many (least common)
![Page 14: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/14.jpg)
FACTS ABOUT ACCESS 2007
Field names Can have up to 64 charactersCan contain letters, numbers, characters and spaces
EXCEPT Periods (.)Exclamation points (!)Accent graves (`) Square brackets ([ and ])
![Page 15: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/15.jpg)
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
(older versions).
![Page 16: DATABASE DESIGN USING MICROSOFT ACCESS. What is a Database? DMS Database management system Database Collection of data organized in a manner that](https://reader035.vdocuments.us/reader035/viewer/2022062500/56649e905503460f94b94af8/html5/thumbnails/16.jpg)
THE ACCESS WINDOW