How to interact with a foreign key in database?

Database Design 1

• How does Data of Database in Database System affect Database Design? How does Metadata of Database in Database System affect Database Design? How does Indexes of Database in Database System affect Database Design? How does Tables of DBMS in Database System affect Database Design? How does Forms of DBMS in Database System affect Database Design? How does Queries of DBMS in Database System affect Database Design? How does Reports of DBMS in Database System affect Database Design?

• Answer:

  Dear fahd, If as your clarification suggests, you are looking for a general overview of relational databases, a tutorial such as this one from Oklahoma University is a good place to start. Dilip's Brief Introduction to Relational Databases: http://www.cs.unc.edu/Courses/wwwp-s98/members/barman/databaseLesson/index.html _______________________________________________________________________ How does Data of Database in Database System affect Database Design? _______________________________________________________________________ Often the database designer would not have much choice in the data that they receive and have to handle. The data and more importantly the relationship between the items of data, determine the “split” of data across the tables. In a sense the type of data also determines the type of reporting that is likely to be requested (a later question) and thus influences design through the idea of planning for efficient data retrieval. However, the type of data and the expected reporting requirements, with some commonsense, almost automatically determines the field type without too many design decisions. For example the first name data is not going to be stored in a field of type integer. It is string/text data. However an order code although primarily numeric may have an alpha prefix and a numeric field would not be appropriate. Similarly one would not store “firstname surname” in the one field because sorting and retrieving is going to be faster if they are stored in separate fields. Another example would be that you would separate zip code and state into their own fields, again for more efficient sorting and retrieval. The size and nature of the data would also affect which database product, server, access means may be appropriate. _______________________________________________________________________ How does Metadata of Database in Database System affect Database Design? _______________________________________________________________________ Metadata is data about the data. It may be primarily for the database designer that includes documentation about the tables, data and data types etc for the user such as in this example from The Wyoming Geographic Information Science Center. www.sdvc.uwyo.edu/metadata/database.html Thus good sources of metadata can make simplify the tasks of designer, maintainer and user. It is far simpler and easier to gain an overview of a complicated database application from Metadata than from inspecting every field, table, relationship and report. _______________________________________________________________________ How do Indexes of Database in Database System affect Database Design? _______________________________________________________________________ The main purpose of indexing is to speed data retrieval. Thus indexes should be created for the most frequently retrieved fields and or combinations of fields. Eg Surname index ______________________________________________________________________ How do Tables of DBMS in Database System affect Database Design? _______________________________________________________________________ Correct table design is fundamental to a relational database. If the data is not correctly arranged within the tables, data retrieval and maintenance can be slower and at worst, almost impossible. Imagine a sales database with customer details, invoice details within the one table and product details in another table with no connecting relationship. Thus for every sale item, the customer details would be repeated – A lot of duplication. Also each product description, price etc would have to be manually typed into the invoice. _______________________________________________________________________ How do Forms of DBMS in Database System affect Database Design? _______________________________________________________________________ Forms are the primary user interface. If there is no postcode field on a form – the data cannot be collected. Similarly, if the form specifying the available reports is missing a report or field, the user cannot generally retrieve the required report. Imagine trying to retrieve sales data if there is are no date fields. _______________________________________________________________________ How do Queries of DBMS in Database System affect Database Design? _______________________________________________________________________ Queries specify the required data to be retrieved and are often the underlying intelligence to the report. As mentioned in the answer to your first question regarding data: queries determine the design of the database as the data must be present in the database in order to retrieve it; furthermore a database designer would also keep efficiency in mind. For example, you may wish to retrieve sales data on product A by all salespersonnel and in region B and compare it to region C for sales dates July through August. Obviously if you have not previously defined regions in terms of postcodes or states or countries – the query will be much more difficult. _______________________________________________________________________ How do Reports of DBMS in Database System affect Database Design? _______________________________________________________________________ Because Reporting depends on the underlying queries to the database, all the previous answers also apply to reporting. Reporting is the user interface to retrieving the data in the database and is vitally important to the user’s perception of the database application. If there “never” seems to be the exact report a user requires, there view of the entire application may be that it is useless. The problem arises because often the user is not capable of generating accurate ad hoc reports, and will depend on the local Information Systems/Database guru. Particularly with financial data, it is very easy to generate a query that should produce the correct data, but doesn’t: “Oh you didn’t want the order date you wanted the sales date or the posted date or the warehouse entry date or the average of the 5 dates!” _______________________________________________________________________ I hope this was these were the kind of answers you were expecting :-) Please select clarify answer for any specific comments that you would like explained. Regards