A database of building material companies might look something like this: At least one customer is required. Every customer is assigned a number of addresses and contact numbers. A default salesperson acts as the liaison between the customer’s company. There are many products that the company sells. Each product is given a price, description, and other details. You can place orders for either one or several products at once. Each product is logically a separate order line. When an order has been completed, it is sent and invoiced. The database automatically populates the invoice number and shipping number. They cannot be edited by users. Each order is assigned a status, such as COMPLETE, SHIPPED or INVOICED. You can also find specific shipping information in the database (bill number, number shipped boxes, dates).
Although one shipment is usually one order, the database can distribute one order between multiple ships. One shipment can also contain more than 1 order. The database also has some limitations. A database cannot contain empty fields, while other fields may only contain certain types information. By definition, a database can contain multiple users. It is common to group users according the roles they play and the security level they have access to. This order management system could have three user groups. The sales department clerks are responsible for entering or changing customer information, while the shipping department employees update and create shipment data. Warehouse supervisors manage products. Each user group has different ways of viewing the information in the database. They can use reports and ad hoc queries to view it from different perspectives. This database will be used throughout this book as examples and exercises. ACME is a simplified production database. The ACME database has 13 tables. A real production database would have more than 100. Cross-References Please refer to Appendix C (The ACME Sample Database), or Appendix D (Installing ACME Database), which provide more details about the database as well as instructions for installation. Database for health care providers A company that provides healthcare services has many offices located in various states. The company employs many doctors, each of whom takes care of multiple patients. Some doctors work in a single office while others are spread out across multiple offices. The database stores information about each doctor’s name, address contact phone number, and specialization. One or more doctors can be assigned for each patient. You can also store specific patient information such as your name, address, phone number and date of birth. Customers can also cancel or schedule appointments, and order prescriptions online. There are certain restrictions. The patient must get approval from their primary doctor to visit a specialist. Patients who order prescriptions should have at the least one valid refill. Which are the most common database users? The database should be accessible via a Web-browser by patients to request prescriptions and to schedule appointments. The database allows patients to access it for all their needs. Doctors can access the database to view information about their patients.
Administrative staff, such as pharmacy assistants or receptionists, can schedule patients and fill prescriptions. This database is more complex and could have more business rules in real life, but we are only trying to provide a rough idea of what information a database might contain. Hybrid databases include the order management and health system databases. The latter, however, is closer to an OLTP. Database for scientific research A scientific database can include a database that is used to research genome research, and other areas of molecular and cell biology research. It has gene maps, genome information, gene catalogs for complete genome sequences, partial genomes, and sequence similarity data among all organisms. It also has information on chemical compounds, reactions and molecular interactions networks. The database only has one user group. All researchers have equal access to all information. This is an illustration of a data-warehouse.
Database for non-profit organizations An easy database for an old automobile club could be created. The database will not be large because it would have fewer members than an average organization. It is necessary to keep members’ personal information like address, phone number and interest. The database may also include information about autos such as their brand, year, color and condition. The club members who own the autos have to be their owners. A member can own one or more cars. Only one member can own a vehicle. The database could only be used by a handful of people, such as the chairman, secretary, assistant and secretary. These two databases aren’t business-critical and don’t need to be run on costly enterprise software. Although the data must be protected and not lost, it is possible to wait for a few days before the backup can be restored.
A free database such mySQL and PostgreSQL is recommended. MS Access is another great option. This part of Microsoft Office Tools includes MS Access. MS Access is compatible with up 15 users.
Author
