Data modeling is a critical activity in system design and involves analyzing the data requirements for a proposed system and representing them visually. During the system analysis phase of development, you may have created an entity-relationship diagram (ERD) or a UML class diagram. The ERD is a much older data modeling structure first developed in the early 1970s. The UML class diagram was developed in the 1990s in response to the rapid growth of object-oriented programming languages and system development methodologies. Although both diagrams are still in use today, the UML class diagram has outpaced the older ERD in new system development.
The UML diagram often represents a bridge between system analysis and system design. One of your final assignments in your system analysis class was probably to create a UML diagram.
Understanding the entities or classes and the relationships between those items helps system designers discuss what data need to be stored and how these data will be used throughout the proposed system. It could be argued that data modeling is the most important step in systems design because this step involves fundamental decisions that impact the overall design and performance of the system.
Data models can be divided into three categories: conceptual, database, and physical models. UML diagrams represent a conceptual model of the data. The conceptual model is the way humans perceive the data and relationships. Conceptual models are then mapped to specific database models. The two most popular database models are the relational and object-oriented models. Once the appropriate database tables or classes are created, the data must be mapped to the physical data model. The physical data model defines how the data is stored within the computer. The physical model describes the storage medium and the location of the data and associated indexes on the various media.
To Prepare for This Assignment
In this Assignment, you will be provided with an ERD based on the functional requirements of a fictitious property management organization. You will map this logical data model into a relational database model. You will use SQL statements to create all the required relational tables, attributes, and data types based on the ERD. The Week 4 Assignment resources provide the needed information to complete the mapping to the database model and create the database tables. In addition to the Learning Resources, you can use your knowledge of data types developed in previous courses to determine the appropriate data type of each of the attributes in the database tables. You may use data types and create table statements from the MySQL, MS-SQL-Server, or Oracle database systems. A few simple internet searches should allow you to obtain the information you need on the data types and create table statements used in each of these three DBMSs. Be sure to specify which DBMS you are using.
Below is a description of a property management organization in need of a computerized system to manage the organization data.
The Alpha-Beta Property Management organization was founded in 1991 by Kamala and Jamal Rodriquez. They started with one duplex apartment building. They lived in one of the duplex apartments and rented the other unit out. By 1998, they had managed to buy two additional rental buildings. Over the years, they continued to leverage their rental income and purchase additional properties. Kamala and Jamal now own 12 buildings with a total of 75 rental units. They have hired several property managers to assist them with the day-to-day operation of each of the buildings they currently own. The managers are each paid a salary and provided a place to live in one of the rental units they manage. Some of the managers are responsible for properties contained in multiple buildings.
Potential renters must undergo a credit check prior to signing a contract. A security deposit is required along with the first month’s rent prior to move in. If rent is not paid on the due date, a late fee is added to the monthly rental rate. Every 10 days the payment is late, another late fee is added to the rent due.
The Alpha-Beta Property Management system will also track Rental Unit and Building maintenance costs. This will allow Kamala and Jamal to print out monthly, quarterly, yearly, and lifelong maintenance costs on each Rental Unit and Building. The Building maintenance costs include landscaping, new roofs, and outside building maintenance. Rental Unit maintenance includes items such as inside paint, window replacement, carpeting, plumbing, etc.
Kamala and Jamal hired a student design team to develop an ERD for the computerized system they want to have developed to track the company information.To prepare for this Assignment, review the Assignment resources included in this week’s Explore section. In particular, review the sample mapping of an Entity-Relationship Diagram to a set of relational database tables.
- Given the Alpha-Beta Property Management ERD, develop a set of SQL create table statements that appropriately map the conceptual data model to a relational database data model.
- Use data types and create table statements from the MySQL, MS-SQL-Server, or Oracle relational database systems. Be sure to specify which DBMS you are using.
- Include all appropriate primary key and foreign key constraints in the create table statements.
0 comments