American City Corporation Database Report

This report analyzes the database needs of American City Corporation, a subsidiary of the Rouse Company. It delves into the three-level ANSI-SPARC architecture for database management systems (DBMS), emphasizing data independence and the roles of data and database administrators. The report also discusses the database system development life cycle, illustrated with an Entity-Relationship Diagram (ERD) showing the relationships between entities like inventory, customers, suppliers, and employees. While sections on advanced SQL, database management systems, and web and data warehousing are marked 'To Be Determined,' the existing content provides a solid foundation for understanding database design and implementation within a business context. The report concludes with a list of references.

Running head: American City Corporation
American City Corporation
CS352-1603A-01
Jivan Washington
July 18, 2016

American City Corporation
Table Of Contents
American City Corporation..........................................................................................................................3
The Database Models, Lanaguages and Architecture..................................................................................4
Database System Development Life Cycle...................................................................................................6
Database Management Systems.................................................................................................................9
Advanced SQL............................................................................................................................................10
Web and Data Warehousing and Mining in the Business World..............................................................11
References…………………………………………………………………………………………………………………………………………….12
2

American City Corporation
The American City Corporation
In 1968 the Rouse Company had formed a Subsidiary Company called American City
Corporation. The company was formed so that it could give advice to older cities to help forma
plans based on the Columbia experience. In 1972 50 organizations used this model approach
that American City Corporation established and in 1970 Hartford Insurance followed and later
signed a 3 million dollar contract with American City Corporation.
3

American City Corporation
Database Models, Languages, and Architecture
Planning is a critical step in creating a database, and as much as jumping right into
creating tables and schemas may be tempting, taking a little bit more time to put everything into
perspective can always save us a lot of grief. In this article, we will look into certain elements of
a database management system, namely: the 3-level ANSI-SPARC architecture, data
independence, data and database administration personnel, and how planning can help us make
the right choices concerning them, right from the very beginning. My company, the American
City Corporation, is considering consolidating it databases to allow for central storage, access,
and development, and therefore, understanding the above listed components of a DBMS is
imperative.
The acronym ANSI-SPARC stands for the American National Standards Institute
(ANSI), while SPARC stands for Standards Planning and Requirements Committee, and is a
design standard for database management systems (Özsu & Valduriez, 2011). This abstract
standard was developed in 1975 and consists of a three-level architecture: the external level, the
conceptual level, and the internal level. Basically, the external level describes part of the
database that is useful to the users. Sometimes, it is normally referred to as the users’ view. All
irrelevant data or all unauthorized data are usually excluded from this view. This provision
implies that users have access to the same personalized data, and changes to a single user do not
affect other users (Özsu & Valduriez, 2011).
The second level, conceptual level, describes the intricate properties of the data stored,
how they are inter-related, and the relevant integrity constraints. It is also good to know that only
the data or database, administrators work at this level and that this level is always independent of
both software and hardware operational (Özsu & Valduriez, 2011). Lastly, the internal level
illustrates how data is actually physically represented in the computer system. It shows how data
is physically stored in both the computer and on the hardware. Together, the three levels ensure
a substantial data independence as will be discussed in the ensuing sections.
It is will be important for the American City Corporation to ensure immunity of its user
applications from any unprecedented changes in definition and the organization of data in its
database (Date & Hopewell, 1971). This requirement can be taken care of in the initial stages of
4

American City Corporation
planning that always determines the level of access and changes that any one user is capable of
making in a database, way before commencing development. Data independence in a three-tier
architecture is usually two-fold (Date & Hopewell, 1971). First level—the logical—data
independence, and second level—the physical—data independence. A database is said to be
logically independent if one can change the conceptual schema without changing the user view.
Adding or removing relationships, attributes, or entities in the conceptual schema should not in
any case warrant the addition or rewriting of the existing applications (Date & Hopewell, 1971).
Other the other hand, the ability to alter the physical schema without altering the conceptual
schema constitutes the second level data independence. For instance, a change in physical
storage should be possible without having to change the inter-relationship between data sets
(Date & Hopewell, 1971).
Another important aspect of a database management system that will be important to the
company is the personnel. It is true that over the past years, many organizations, including the
American City Corporation, have failed to recognize the essential differences in the specialties of
a data administrator and a database administrator (Ramakrishnan & Gehrke, 2000). As a result,
there has been much confusion in data acquisition and processing in these institutions, a problem
that I believe can be solved in the very early stages of creation by outlining roles and skill sets of
every personnel in this field. Just to give a brief description, a database administrator manages
and stores information in a database. He is responsible for developing a physical database,
maintaining database software, and data dictionary, whereas a data administrator is responsible
for defining data names, elements, and relationships. They also design and manage data
management software, validate data, and ensure their overall security (Ramakrishnan & Gehrke,
2000).
From the above discussion, it is reasonable to conclude that the duties of data and
database administration should never be left to a single individual or subgroup, and must be
separated. These responsibilities require different skill sets and autonomy that can only be
handled effectively by separate parties. Assigning all of these tasks to a single person only
implies that certain management aspects will be overlooked. Overall, planning is integral as it
will provide the necessary clues as to the kind of a system which is required by this real estate
company and the personnel needed
5

American City Corporation
Database System Development Life Cycle
The following essay entails information about Entity-Relationship Diagram (ERD) that displays
the relationship between several entities such as inventory movements from the organization to
the consumers. The relationship between each of the entities is unique depending on the
information transmitted backward or forwards as displayed in the diagram. The complex
relationships that detail the transfer of information across the entities mentioned earlier
necessitate the need for advanced enhanced diagram tools. The ERD included below covers the
primary grounds for the relationship existing between the entities, albeit much of the
relationships require further in-depth documentation to record the information in a proper and
efficient manner. The relationship between the inventory movement and the information shared
in between requires advanced diagramming tools to document the fine details of such
movements without the risk of misrepresentation. ERDs are synonymous with students and
professionals thus they do not have a predetermined outlay that augurs universally with its users.
Consequently, the potential for misunderstandings and misplaced inferences creates a
disadvantage for the use of ERDs and EERDs (De Lara & Vangheluwe, 2002).
6

American City Corporation
Figure 1 showing the Entity-Relationship Diagram for the protracted company considered for this
assignment
The Diagram represents the relationship between several entities that are integral to the
functionality of the company in pursuit of its goals and objectives in the long term and short
term. The entities in question include internal support, Customer interaction, employee
communication, the inventory, and supplier interaction. Each of the entities holds a special
relationship with them that differentiates them each other. The diagram above indicates a distinct
7

American City Corporation
data dissemination pattern stemming from the company inventory through the internal support,
the customer interaction, and supplier interaction. The employee entity occupies a unique locus
in the complex relationship of the ERDs that allow it to supersede the other relationships with the
company inventory (Song, Evans & Park, 1995). The relationship documented in the image
above capitulates into finer details as discussed below.
Customer Interaction: This section contains information about the client in regards to what he or
she obtains from the inventory list through acceptable means of acquisition. The customers
primarily engage in product purchase thus this section covers area such as product purchase, date
of purchase, and the total of purchase (Suraweera & Mitrovic, 2004).
Supplier information: This section handles the supplier aspect of the relationship, which affects
the outlay of the inventory through directly influencing the inflow or outflow of products.
Consequently, the information disseminating from this section is essentially about the product
handled by the company or organization of choice. Thus, despite their variety, the data contained
covers three basics fields that include the product sold, date of sale, and total of sale.
Internal support: This section details the data covering the salary and the support area of the
entire set up. The section is independent of the other section thus its direct link with the
inventory and any subsequent changes.
Customer interaction: The diagram also presents the client's interaction in level with supplier
interaction as they jointly influence the actions of the employee and thus the internal support.
This section covers product specialty, hours of training, and commission rate for the employees
in their daily deals with the customer. This group is synonymous with numerous transaction thus
the "many" interaction symbol indicated by the inverted trident (Suraweera & Mitrovic, 2004).
8

American City Corporation
Employee (customer interaction): This final section involves a broad category of interactions in
the ERD by covering areas such as product specialty, hours of training, and commission rate.
The employees play an integral role in the validity of the actions of the company, the suppliers
and the consumers at large.
Database Management Systems
To Be Determined
9

American City Corporation
Advanced SQL
To Be Determined
10

American City Corporation
Web and Data Warehousing and Mining in the Business World
To Be Determined
11

American City Corporation
References
Date, C. J., & Hopewell, P. (1971). File definition and logical data independence. In SIGFIDET
Workshop (pp. 117-138).
Özsu, M. T., & Valduriez, P. (2011). Principles of distributed database systems. Springer
Science & Business Media.
Ramakrishnan, R., & Gehrke, J. (2000). Database management systems. McGraw-Hill.
De Lara, J., & Vangheluwe, H. (2002, April). AToM3: A Tool for Multi-formalism and Meta-
modelling. In International Conference on Fundamental Approaches to Software
Engineering (pp. 174-188). Springer Berlin Heidelberg.
Song, I. Y., Evans, M., & Park, E. K. (1995). A comparative analysis of entity-relationship
diagrams. Journal of Computer and Software Engineering,3(4), 427-459.
Suraweera, P., & Mitrovic, A. (2004). An intelligent tutoring system for entity relationship
modelling. International Journal of Artificial Intelligence in Education, 14(3, 4), 375-417.
12