Introduction
Gadget First, a retail store for electronic products based in Manchester, sells different categories of electronic products like DVDs, Laptops, Mobile phones, Hi-fi cameras etc. Gadget First has many suppliers who supply different kinds of electronic products under various categories of electronic goods. Since there are too many suppliers to supply the various products, Gadget First wants to automate its process of inventory control like checking the availability of stocks, maintaining re-order levels, keeping a record of suppliers for each items, maintaining a record of all transactions between the store and the supplier and the issue of order forms for each order placed with suppliers.
Problem Analysis
Gadget First’s staff seems to be having a problem with keeping track of the available stock and maintaining the re-order levels. Also, since there are too many suppliers with any one supplier being able to supply many items, Gadget First may need a separate supplier database to resolve this issue. Another problem that Gadget First faces is – keeping track of all the orders made – like for example, the date on which the order was made, the quantity ordered and the supplier to whom that order was placed. The proposed database design should address all these issues for successful implementation of the database system.
Assumptions made
It is assumed that Gadget First has the following system requirements at its disposal for successful implementation of the database system:
A server that runs the MYSQL RDBMS server application.
Client systems with MYSQL client query tool to log on to the database and initiate queries and generate reports.
Constraints
Possible constraints for this project are:
The resources to implement the database system with the requirements described under “Assumptions”.
As a result of implementation of this system some of the record keeping staff in Gadget First may be laid off or trained to handle the new system and this may lead to resistance from the staff who are resistant to change.
Normalization of Gadget First’s Inventory Database
Normalization is done to organize data in a database in an efficient manner. The goal of normalization is to eliminate redundant or duplicate data and to make sure that only related data is stored in a table. There are three entities in Gadget First’s inventory database; they are Items_In_Stock entity, Supplier_Details entity and Order_Details entity. A normal form is a set of guidelines to normalize a database and the normal forms range from the first normal form (1NF) to the fifth normal form (5NF). In this case study, Gadget First’s inventory database is normalized up to the third normal form (3NF).
First Normal Form
The three entities have various attributes like Item Code, Item Description, Price, Quantity_In_Store, Re-Order Level, Re-Order Quantity, Supplier Name, Address, City, Phone Number, Order Number, Item Description, Quantity Ordered, Order Date and total amount as attributes.
In the first normal form you need to create separate tables for each group of related data and identify the primary key. Therefore, for the first entity Items_In_Stock we will group all the data that is related to this entity. The related data for this entity are: Item Code (field type: alphanumeric), Item Description (field type: character), Price (field type: numeric), Quantity_In_Store (field type: numeric), Re-Order Level (field type: numeric), Re-Order Quantity (field type: numeric). In this dataset for Items_In_Stock, only the Item Code column will contain unique data, all other columns will have repeating data. For example, there may be more than one same item description for two items in the table; two items in the table may have the same quantity etc. Therefore, only the Item Code column is unique for each item. Therefore, we set the Item Code as the primary key for this table. There are no further columns with a unique set of data; therefore, there are no secondary keys for this table.
For the Supplier_Details entity group the following sets of related data, Supplier_ID (field type: alphanumeric), Supplier Name (field type: character), Address (field type: alphanumeric), City (field type: character), Phone Number (field type: numeric). The only unique column in this entity is the Supplier_ID column; all other columns will have repeating data. Therefore, the Supplier_ID is the primary key for this table.
For the Order_Details entity group the following sets of related data, Order_ID (field type: alphanumeric), Item Description (field type: character), Quantity Ordered (field type: numeric), Order Date (field type: date), Total Amount (field type: numeric). The only unique column in this entity is the Order_ID row; all other columns will have repeating data. Therefore, the Order_ID is the primary key for this table.
Second Normal Form
In the second normal form, besides meeting the requirements in the first normal form (1NF), you have to create relationships between the tables using foreign keys. A foreign key matches the primary key of the other tables. The foreign key is used for cross-referencing between tables and it is mainly inserted in dependent tables. Therefore, among the three tables you have to identify the dependencies. In the case of Gadget First’s inventory database, the Supplier_Details table is dependent on the Items_In_Stock table to determine the suppliers whose items have crossed the re-order levels. Therefore, insert the primary key of the Items_In_Stock table into the Supplier_Details table as a foreign key. Now, the primary key for the Supplier_Details table is a combination of the primary key of this table and the foreign key. Likewise, the Order_Details table is dependent on the Items_In_Stock table to determine the items to be ordered that has crossed the re-order levels and the Supplier_Details table for information on suppliers for a given item. Therefore, insert the primary keys of both the Items_In_Stock table and the Supplier_Details table into this table as foreign keys. Now, the primary key for the Order_Details table is a combination of the primary key of this table and the foreign keys.
Third Normal Form
Remove columns that are independent of the primary key. In this assignment the Item_Description column and the Total_Amount columns in Order_Details are independent of the primary key, remove those column and the Gadget First Inventory database is normalized up to the third normal form (3NF). The following diagram shows the 3NF-normalized database.
Entity – Relationship Diagram (E – R Diagram)
In the Entity – Relationship diagram for this problem, there are three entities and there are only two types of relationships between the entities, they are, many-to-many type of relationship and one-to-one type of relationship. The entities are Items_In_Stock entity, Supplier entity and Order_Details entity. The Items_In_Stock entity has a many-to-many relationship with the Supplier entity because one supplier can supply many items and many suppliers may supply one item itself. The Items_In_Stock entity also has a many-to-many relationship with the Order_Details entity because many items may be ordered in one order given to a supplier and one item itself may be part of many orders made to different suppliers. There is a one-to-one relationship between the Supplier entity and Order_Details entity because only one order is given to one supplier and one supplier supplies items only for one order. After normalizing the data we get three tables for the three entities and their attributes are listed as columns in each table with primary to identify each item in the table and foreign keys to cross-reference one table with another. A double-edged arrow indicates a many-to-many relationship and a straight-line indicates a one-to-one relationship in the following diagram:
Query Transactions
For example, let us assume for the first entity Item_In_Stock the following field lengths: Item_Code (field type: alphanumeric, length: 8), Item_Description (field type: character, length: 30), Price (field type: numeric, length: 5), Quantity_In_Store (field type: numeric, length: 5), Re_Order_Level (field type: numeric, length: 5), Re_Order_Quantity (field type: numeric, length: 5).
For the second entity Supplier_Details, Supplier_ID (field type: alphanumeric, length: 8), Item _Code (field type: alphanumeric, length: 8), Supplier_Name (field type: character, length: 30), Address (field type: alphanumeric, length: 30), City (field type: character, length: 20), Phone_Number (field type: numeric, length: 5)
For the Order_Details entity we group the following sets of related data, Order_ID (field type: alphanumeric, length: 8), Supplier_ID (field type: alphanumeric, length: 8), Item _Code (field type: alphanumeric, length: 8), Item Description (field type: character, length: 30), Quantity Ordered (field type: numeric, length: 5), Order Date (field type: date, length: 8), Total Amount (field type: numeric, length: 6).
Test Data: Item_In_Stock
| Item_Code |
Item_Description |
Price (field type: numeric, length: 5), |
Quantity_In_Store (field type: numeric, length: 5), |
Re_Order_Level (field type: numeric, length: 5), |
Re_Order_Quantity (field type: numeric, length: 5). |
| DVDTh001 |
DVD Toshiba |
200 |
300 |
100 |
400 |
| DVDSn001 |
DVD Sony |
250 |
200 |
250 |
300 |
| TVSn001 |
TV Sony |
300 |
100 |
150 |
300 |
| TVTh001 |
TV Toshiba |
350 |
100 |
250 |
300 |
| Wash001 |
Washing Machine |
3000 |
0 |
100 |
150 |
Test Data: Supplier_Details
| Supplier_Id |
Item_Code |
Supplier_Name |
Address |
City |
Phone |
| Ant001 |
DVDTh001 |
Antony |
No. 23 luke st. |
Glasgow |
66666 |
| Ant002 |
TVSn001 |
Antony George |
No. 25 Luke st. |
Glasgow |
7777 |
| Pam401 |
DVDSn001 |
Pamela |
Martha St. |
Middlesex |
8888 |
| Pam401 |
Wash001 |
Pamela |
Martha St. |
Middlesex |
8888 |
| Sam402 |
TVTh001 |
Sam |
No.6 John St. |
Gloucester |
9999 |
| Sam402 |
Wash001 |
Sam |
No.6 John St. |
Gloucester |
9999 |
| Tom403 |
DVDSn001 |
Tommy |
No.8 Martha st. |
Middlesex |
111112 |
| Tom403 |
DVDTh001 |
Tommy |
No.8 Martha st. |
Middlesex |
111112 |
| Pete404 |
TVSn001 |
Peter |
No.7 Martha St. |
Middlesex |
101012 |
| Pete404 |
TV Th001 |
Peter |
No.7 Martha St. |
Middlesex |
101012 |
Test Data: Order_Details
| Order_Id |
Item_Code |
Supplier_ID |
Quantity_Ordered |
Order_Date |
| Ord001 |
TVTh001 |
Ant001 |
300 |
8/14/2010 |
| Ord002 |
TVSn001 |
Pete404 |
300 |
8/14/2010 |
| Ord003 |
DVDSn001 |
Tom403 |
300 |
8/14/2010 |
| Ord004 |
Wash001 |
Pam401 |
150 |
8/19/2010 |
| Ord004 |
DVDTh001 |
Pam401 |
100 |
8/19/2010 |
Screen Design Of The Actual Database
The name of the database is inventory_control and there are three tables in this database, they are, item_in_stock table, supplier_details, order_details as shown in the following screen.
The item_in_stock table has item_code as the primary key and the supplier_details table has combination of supplier_code and item_code as primary keys. The order_details table has a combination of order_id, item_code and supplier_code as primary keys. All these tables are linked to each other foreign reference keys shown in the database normalization diagrams in the previous sections.
Queries
List all items in stock:
Query: select * from item_in_stock;
List all of the items in each category. Query: select * from item_in_stock where item_code=’dvdsn001’.
List all of the items not in stock. Query: select * from item_in_stock where quantity_in_stock=0;
List all of the items not in stock in each category of item. Query: select * from item_in_stock where item_code=’wash001’ and quantity_in_stock=0;
List all of the suppliers who supply a particular item.
Query: select * from supplier_details where item_code=’tvsn001’;
List all of the suppliers from a particular city.
Query: select * from supplier_details where city=’middlesex’;
List all the transactions made between particular periods.
Query: select * from order_details where order_date between ‘2010-08-12’ and ‘2010-08-15’;
List all of the transactions made by a particular staff.
Query: select * from order_details where staff_name=’Jonathan Stone’;
List all the orders generated during a particular period.
Query: select * from order_details where order_date between ‘2010-08-12’ and ‘2010-08-15’ and order_status=’Generated’;
Shortcomings/bugs if any:
None.
Conclusions and Comments
A thorough understanding of database structures was achieved. I learnt how to identity entities and the relationship between entities, and also to identify the attributes for each entity. Also, I learnt how to normalize the database structures, identify primary keys and how to establish relationships between tables through the use of foreign keys. I also learnt how to design an E-R diagram and also got a better understanding about the purposes of these diagrams during the coursework.
Part 1
The different data model structures, their comparisons and contrasts, and their contribution to database development.
What is a data model? What are the different data model structures?
The process of defining and analyzing data requirements to support the business process of an organization is known as data modeling. Every requirement of data is stored as a conceptual model with data definitions. The implementation of the conceptual model is known as the logical model and the implementation of the conceptual model requires the use of multiple logical data models. There are different data models and techniques used to standardize data and to make the data consistent, predictable and manageable.
A data model may implement one of the following three schemas:
Conceptual schema: Describes the semantics used in a domain, like the entities in a domain and the relationships between the entities. A conceptual model expresses the facts of a domain by using a model.
Logical schema: Describes the structures of information within a domain, like for example, descriptions of tables, classes and XML tags.
Physical schema: Describes the data storing mechanisms used in the real world like the number of partitions required, table spaces and the number of CPU’s required.
The above three perspectives are independent of each other and as such any change in technology may affect the physical schema but the logical and conceptual schemas remain intact.
A detailed data model of a database is known as a database design. The different data modeling methodologies are:
Bottom-up models: this modeling methodology is used mostly in reengineering efforts. The models bases on this methodology start with the existing data structure forms, fields and reports. This is more application specific and an incomplete modeling methodology. The models that are based on this methodology are known as physical data models.
Top-down models: Logical data models adopt this methodology. Models following this methodology follow an abstract way of gathering information from subject matter experts (SME). Any system designed by using the logical model may not implement all the aspects of the logical model. However, this model serves as the reference point for these systems.
Compare and contrast the different data models and examine their contribution to database development.
The different data models, their comparisons, contrasts and their contribution to database development:
The Entity –relationship (E-R) model: In this model data is described in terms of entities and relationships. The E-R model is an abstract, conceptual representation of structured data. The E-R model is a relational database modeling method used to produce a conceptual model of a relational database system. This model is based on the top-down methodology.
Generic model: This model defines the general relationship type along with the components they try to relate. The generic model creates generally a binary relation and a part-whole relation. A generic model allows classification of any individual thing and specification of a part-whole relationships in a project. By providing for an extensible list of relational types this model is more like a natural language unlike other models that have fixed and limited scope.
Semantic model: Any database management system’s logical structure cannot satisfy the requirements of a conceptual definition. The need for a conceptual view brought into focus the semantic model. This modeling technique defines the meaning of data in terms of the inter-relationship that it has with other data. A semantic model can used for purposes like planning of data resources, building of shared databases, and integration of existing databases. The semantic model integrates relational concepts with powerful abstraction concepts.
Examine case material that focuses on developments such as data mining and data warehousing.
I happened to examine a case material on data mining known as “Assessing Loan risks: A data mining case study. “ by Rob Gerritsen. Data mining involves building the data model, designing an algorithm to implement the model. In this case study, Gerritsen’s company conducted a preliminary study of the data extracted from USDA data warehouse. The sample contained nearly 12,000 records of current mortgages for single-family homes. The sample data included information on the loan amount, payment size, lending date and purpose, the type of asset, the borrowers personal details, and his region. Different data mining models were used to predict the outcomes. The different models can be classified into predictive and descriptive models. Predictive model predicts the value of a particular attribute. The models can predict a customer’s likelihood of switching to a different competitor, fraudulent insurance claims etc. Descriptive models use clustering and association. Clustering bring together similar people, things etc. Association models involves researching on how frequently two or more things happen at the same time. Once you determine the type of model to be used, then the next important task is to design an algorithm to implement the data-mining model. The model used should produce accurate results this is done by comparing the predicted values with the actual values. But the downside of any data-mining algorithm is that there is no algorithm that produces accurate results. Once you have decided on the data-mining model and the algorithm to be used then the next part is identifying the test database. In this case some 12,000 records of the USDA were examined. Gerritsen’s company used the Naïve Bayes algorithm, which requires binning of numeric values. The algorithm binned all numeric values into 5 bins from $0 to $60,000. It then assigned each borrower’s loan amount to a pay-amount bin, which the data-mining algorithms use. The bins were divided into 5 ranges of 12,000 each, starting from $0 to $11,999. The binning ranges were repeatedly adjusted until a satisfactory binning range was found, which improved the model’s accuracy to 67%. Later, irrelevant issues like ‘total amount due’ were pruned from the data that was mined, and decision trees were refined to arrive at the most accurate factors that caused the high-degree of loan defaults with the USDA. In this case data mining helped determine the factors that caused loan defaults. The important factors that influenced loan defaults at the USDA were determined as loan type, type of security, marital status and monthly payment size.
Data mining can be used to predict the exact causes as to why certain events occur and suggest ways to face or avoid those events.
The ability to gather data into optimized databases is known as data warehousing. Data warehousing can be defined as “coordinated, architected and periodic copying of data from various sources into an environment optimized for analytical and internal processing”, Simon, Alan. R (2005).
I happened to examine another case study on data warehousing by Lolopop partners. They served as principals in a data warehouse effort in which there were many users of those data warehouses. During the requirements gathering the lolopop partners found out that high-quality was the number one objective of many users. Even the other objectives of the users were also related to quality. The lolopop partners gathered the requirements into a set of the following objectives.
Create quality information that can be combined with other data sources.
Timely response to request for data
Consistent relation of information
Easy access to information
Make the management to formulate and apply policies and procedures
Manage incoming data from non-integrated sources – This means that data from multiple unrelated sources requires a plan to convert electronic data, manage imaging and document inputs, manage workflow and manage the analysis of data.
Creating reports that are reproducible
The Lolopop team found out that the existing data warehouse systems were used like a bridge to data rather than make an effective use of a company’s information. Therefore, the Lolopop team designed a data warehouse solution that supported complete data communication, command and control capability, and ability to assemble and analyze data by using quality and analytical standards. Therefore, the Lolopop team had to set the quality standards. The first thing they did in their effort to achieve quality was initiating quality concepts.
Quality concepts: A data warehouse must accurately give the truth for the purpose of any meaningful analysis and decision-making. Every data element should be made traceable to its sources and such sources should be verified for integrity.
Data acquisition: Data storage should be unique. Previously analyzed data should be re-producible, any changes in data should be reported with along with the day the change was made and with an explanation of the differences made.
Routing and scheduling: The routing and scheduling mechanism processes the incoming data based on certain rules like whether the objectives can be met or cannot be met or if a remedial action plan is presented.
Analytics: In this one Lolopop component populates the dataset with data elements meeting specified quality standards. Another builds the analysis plan, selects analysis tools and sets the calculations to be performed against the dataset instantiation.
Different approaches to database design.
The different approaches to database design are:
Top-down approach, and
Bottom-up approach
Top-down approach
Logical data models adopt this database design methodology. Models following this methodology follow an abstract way of gathering information from subject matter experts (SME). Any system designed by using a logical data model may not implement all the aspects of the logical model. However, the top-down approach serves as the reference point for these systems.
Bottom-up approach
This database design approach is used mostly in reengineering efforts. The models that are based on this methodology start with the existing data structure forms, fields and reports. This is an application specific and an incomplete design methodology. The models that are based on this methodology are known as physical data models.
Part 2
Apply data analysis and design techniques to develop a fully relational database with minimum of six tables.
For this question, we will take the case study of Gadget First provided for the assignment. There are already three tables in Gadget First’s inventory control database to keep track of items in stock, supplier details and order details. Now let us extend this case study to assume that Gadget First also wants to keep track of all client orders. Assuming that Gadget First is a wholesale dealer in these electronic goods and it has regular clients who order electronic items only from Gadget First. If this is the case, then Gadget First’s inventory control database will have two additional tables, they are, client order details table and client details table. Suppose if some of the customers return some of the defective goods back to Gadget First, then Gadget First needs to keep track of all the goods returned inwards. For this, a separate table is necessary, namely, returns inwards table. Now since there are six tables in the inventory control database we will implement the data analysis and design techniques on this database.
First Normal Form
There are six entities in Gadget First’s inventory database; they are Items_In_Stock, Supplier_Details, Order_Details, client_order_details, client_details and returns_inwards entities. The first three entities have been normalized already now you need to normalize the remaining three entities in this phase.
In the first normal form for client_details, group together all related information for this entity. The related data are customer code (alphanumeric), customer name (character), address (character), city (character) and phone number (numeric). In this dataset for client_details, only the customer code column will contain unique data, all other column will have repeating data. Therefore, we set the Customer Code as the primary key for this table. There are no further columns with a unique set of data; therefore, there are no secondary keys for this table.
For the customer_order_details entity we group the following sets of related data, customer order id(field type: alphanumeric), item description (field type: character), price (field type: numeric), quantity purchased (field type: numeric) and total amount (field type: numeric), order status (field type: character). The only unique column in this entity is the client order id column; all other columns will have repeating data. Therefore, the Client_order_id is the primary key for this table.
For the Returns_inwards entity we group the following sets of related data, returns inwards id (field type: alphanumeric), item description (field type: character), Quantity returned (field type: numeric) and the Return_Date (field type: date). The only unique column in this entity is the returns inwards id column; all other columns will have repeating data. Therefore, the returns inwards id is the primary key for this table.
Second Normal Form
In the second normal form, among the three tables to be normalized you have to identify the dependencies. In the case of Gadget First’s inventory database, the customer_order_details table is dependent on the Items_In_Stock table to determine the availability of items and the customer_code for retrieving customer details. Therefore, insert the primary keys of the Items_In_Stock table and the customer_details table into the customer_order_details table as foreign keys. Now, the primary key for the customer_order_details table is a combination of the primary key of this table and the foreign keys.
Likewise, the returns inwards table is dependent on the customer_order_details table to identify the order information for the item being returned by the customer. Therefore, insert the primary key of customer_order-details table into this table as a foreign key. Now, the primary key for this table is a combination of the primary key of this table and the foreign keys.
Third Normal Form
Remove columns that are independent of the primary key. In this assignment remove the Item_Description column and the Total_Amount columns in client_order_details and the item description column in returns_inwards, as they are independent of the corresponding primary key. The Gadget First Inventory database is now normalized up to the third normal form (3NF). The 3NF-normalized tables are represented in the following diagram.
Entity – Relationship Diagram (E – R Diagram)
In the Entity – Relationship diagram for this problem, there are six entities and there are only three types of relationships between the entities, they are, many-to-many type of relationship, one-to-many and one-to-one type of relationship. The entities are Items_In_Stock entity, Supplier entity and Order_Details entity, client_details entity, client_order_details_entity and the returns_inwards entity. The Items_In_Stock entity has a many-to-many relationship with the Supplier entity because one supplier can supply many items and many suppliers may supply one item itself. The Items_In_Stock entity also has a many-to-many relationship with the Order_Details entity because many items may be ordered in one order given to a supplier and one item itself may be part of many orders made to different suppliers. There is a one-to-one relationship between the Supplier entity and Order_Details entity because only one order is given to one supplier and one supplier supplies items only for one order. Similarly, there is a many-to-many relationship between items_in_stock and client_order_details entity, one-to-many relationship between customer_order_details and customer_details entities, and a one-to-many relationship between customer_order_details and returns_inwards entities. After normalizing the data we get six tables for the six entities. The attributes of the six tables are listed as columns in each of these tables. Each table has a primary key to identify each item in the table and a foreign key to cross-reference one table with another. A double-edged arrow indicates a many-to-many relationship and a straight-line indicates a one-to-one relationship. A simple arrow indicates a one-to-many relationship in the following diagram:
Verify that a design meets user requirements and provide justification of the database design.
The E-R diagram above follows the top-down approach and it is follows a logical schema of designing a database. By using database normalization I have verified that the design meets user requirements. E-R diagrams are widely used in software engineering to determine the most appropriate design for database in a relational database system. Database normalization ensures that there are no duplicate fields in the database and only the relevant details are stored in a database.
Part 3
Query Transactions
List all the items ordered
Query: Select * from customer_order_details
List all orders for a particular item
Query: select * from customer_order_details where item_code=’DVDth001’;
List all items for a particular order
Query: select * from customer_order_details where client_order_id=’IBM0001’;
List all customers
Query: select * from cust_details
List all items returned
Query: select * from returns_inwards
List all items returned by a particular order
Query: select * from returns_inwards where return_id=’ret0010’;
Part 4
The documentation provided above supports the implementation of the database. Through the process of normalization, the verification of data has been carried out and only relevant fields are incorporated in the tables, there are no duplication of fields in the database and no duplicate keys. The validation process has been taken care of through restriction on the type of data entered. For example, in customer name only characters can be entered when you specify the field type as character. To prevent the field from showing null entries specify the command ‘NOT NULL’ at the end of a query for adding fields to a table. A screen shot of the validation procedure is shown below:
Evaluation of testing techniques and application of one of the testing techniques to database design.
Many testing techniques can be applied on a database design to uncover errors if any. The different testing techniques are unit testing, black-box testing, database re-factoring etc. In this assignment an evaluation of the unit testing and database re-factoring technique is done.
Unit testing technique: This testing technique is used in database design and provides a structured and automated way of testing each part of a database system. Mostly developers who develop a component do the unit tests and would write the test cases for the component. The testing of these components in an isolated manner to ensure component behavior before integrating with the main system is known as unit testing. Unit tests can be run on database stored procedures, functions and triggers.
The different types of unit tests are:
Feature tests: Testing the core features of the database from the database user’s perspective is known as a feature test.
Schema test: This test is used to test the database schema. Testing a view to see if it returns the expected results etc are examples of a schema test.
Security test: Testing the security of the database is done in this phase. Here you test to see if unauthorized users gain entry to the database by testing the database using negative tests.
Stock-Data test: Data that is used infrequently is known as stock data and many databases have these seed data. This test ensures that your seed data exists in your database
The unit testing technique is a very good technique to test your database design. This testing technique completely tests all the components of a database and is more advantageous than manual testing.
Database Re-factoring technique: This is a technique in which a simple change to a schema improves the database design but retains the behavior and semantics of a database.
The purpose of database re-factoring is to:
Safely fix existing legacy databases: Use database re-factoring to improve data and the quality of data in legacy databases using this technique. This is the only technique that can be used to improve legacy databases.
Support evolutionary development: Most of today’s development processes support evolutionary programming and therefore database design also must evolve. Database re-factoring techniques help database designs to evolve.
I applied the unit testing technique on my database design, and the following screens show how I did that and whether my database design passed the test.
First, add the test in the unit testing application:
Name the test as:
Add a row count condition
Set the number of rows to test to 15.
Run the test
And the inventory_control database passed the test.
References:
Gerritsen, Rob. “Assesing Loan Risks: A Data Mining Case Study“
Simon, Alan R. “Data Warehousing For Dummies”.
Wesenberg , David P & . Peterson , Joanne E. “L o l o p o p D a t a W a r e h o u s e C a s e S t u d y” Abator Information Services and Lolopop Partners, 2005.
FIPS Publication 184 released of IDEF1X by the Computer Systems Laboratory of the National Institute of Standards and Technology (NIST). 21 December 1993
Semantic data modeling” In: Metaclasses and Their Application. Book Series Lecture Notes in Computer Science. Publisher Springer Berlin / Heidelberg. Volume Volume 943/1995.
Whitten, Jeffrey L.; Lonnie D. Bentley, Kevin C. Dittman. (2004). Systems Analysis and Design Methods. 6th edition.
Len Silverston, W.H.Inmon, Kent Graziano (2007). The Data Model Resource Book. Wiley, 1997.
American National Standards Institute. 1975. ANSI/X3/SPARC Study Group on Data Base Management Systems; Interim Report. FDT (Bulletin of ACM SIGMOD) 7:2.