Mojahed Ibrahim

Referential Integrity: ensures that the relationship between two table remains synchronized, we cannot add values to the table containing foreign key unless there is a corresponding value in the linked table.
Should foreign key in a table have only unique values? Though it is preferred to have unique values in the foreign key as it becomes easy to map to the table it is linked to, in many to many relationships it is evident that foreign key does not only have unique values.
When is Normalization not preferred? When the databases we are dealing with are highly complex and have too many tables and the queries have too many joins then it not preferred to Normalize and also when the time is limited and when you want an application to be ready quickly, normalization is not preferred.

– Referential integrity ensures stable relationships between tables by maintaining consistency in values across linked tables.

– Foreign keys need not be unique (specifically in many-to-many relationships), although mapping can be made easier if unique values can be maintained.

– The level of complexity is the primary factor when evaluating whether normalization is preferred. Increasing complexity increases the time required for normalization. Be sure to weigh the additional investment to normalize the data against the value that data will provide if normalized.

Question: Name one key difference (there are more than one) between an ERD and a schema. For your reference, look at the Relational Modeling slides from last week.

Answer: In an ERD, each entity and its attributes and relationships are drawn out in a graphical display. In a data schema, all of those entities, attributes, and relationships are represented; however, if two entities have a many-many relationship then another table must be created in the schema using the primary keys as foreign keys in order to account for the relationship of the data. This is true for each many-many relationship in a data model.

In my position as graduate coordinator I will need to join the table of my current PhD students and the table of our departments program of study to see which students completed their preliminary exams and what the results were.

Joins need to be used when you want to retrieve information from two or more tables. Taking an example of an online bookstore, if we have two tables books and orders , we need to join both these tables to find how many orders each book has . Similar to this, using customers and orders, we can find which customer made how many orders. Using all three tables, we can also find which person bought what books.

A key difference between an ERD and a schema is an ERD is more of a visual representation of relationships within a system, while a schema can be thought of as the behind the scenes design of the system in tabular format.

I actually had a problem similar to question one during my summer internship. An organization was converting files from an FTP (file transfer protocol) server to an SFTP server (secure file transfer protocol). In order to do so, the organization had to merge data from two servers, each of which captured slightly different data. One server tracked a customer registration ID number, along with contact name and email address, while the other tracked a merchant ID number and most recent user activity and login information. In addition, some customers were located in both servers, whereas others were only in one or the other. I had to join the two tables together (unfortunately I hadn’t yet learned SQL!), by linking their ID numbers, to find out the total number of customers in the two databases combined, and their unique information.

From my understanding the key difference between an ERD and a schema is that an ERD is a diagram that outlines the structure of the database, it shows the relationships between the entities and their attributes and it is created as a very first step; whereas a schema is the actual database comprising a set of tables, a schema is created based on the outline provided by an ERD.

In my line of work, client spending information is captured when an insertion order (insertion ID) is generated at the vendor/partner level (Partner ID). Multiple vendor “buys”, typically identified by an estimate ID, roll up to a specific client (Client ID). Lastly, client activity is rolled up for internal use by account team (Dept./Employee ID). The joining of information within these tables allows the agency to analyze spending patterns across media types (i.e. TV vs. Paid Search), client types (i.e. Pharma vs. CPG) or internal departments/employees (i.e. Digital vs. Direct Response).

The most recent example I can provide to joining information in two data sets is that I am scripting a cover letter to send to my campers from last year’s Summer program. I am typing a single one page template but then need to swap out the last line with a specific anecdote tailored towards the reader. So I need to compile all the students(over 140) in one table, list their location, and then join this data set with a data set including my one line camper specific ‘hook.’ I will then send both tables, and the cover letter to an intern in my office to have them customize every letter.

In my opinion the difference between ERD and schema is that ERD is just a diagram that tells us the relationship between different entities and the attributes in each entity and how one entity is connected to another. But schema is the actual database that has the values from which we can retrieve whatever value is required by creating queries.

In some work that I did this past summer working on an electronic health record system, there were so many different tables of information that needed to constantly be joined. In fact often times these tables were stored in different databases and would be packaged to share with other practitioners looking to retrieve them. These tables could include allergies, medications, patient demographics, lab results, etc. Joining tables successfully is critical for safe and effective patient care.

During my summer internship, I researched the production costs associated with various merchandise products from a growing startup. Rather than having one excel document for each product, I compiled all their data in relation to production ranging from duty classification and percentage to the price tier ordering for each one. In this way, one can see the landed cost of each product at different quantities and how long it would take for the product to reach its destination all on one master sheet.

Q1:
In the movie database, I joined customer and rental tables in order to find out the individual rentals of customers.

In a past job, we were working to combine our databases on Refugee Resettlement Inflows in Chicago. We had one database of job places and the other with refugee demographics. We wanted to figure out if there were any correlations between country origin and job placement. Therefore, we had to combine the two databases.

Q2:
Schema is the actual database while, ERD is just a diagram showing abstract concepts.

Q1
The idea of joins allows users to retrieve information from two or multiple tables. Take for example in case of a hotel website. Imagine two tables:

Profile Table: a table which has customer profile information such as name, address, passport/nationality details and credit card

Transaction Table : a table which has customer transaction ID, date of arrival, date of departure , number of rooms, bed type preference, number of adults

When we join these two tables we get a number of important things. For example; a hotel can plan for the registration process formalities which they have to follow when letting a room to a guest who may be a foreign national to meet all government regulations

Q2
ERD to me is a visual representation/ diagram/ chart that outlines the structure of the database and shows the various connections and relationships between entities and their attributes. This is also the formative step of database design. Schema to me represents the actual database with tables and takes instructions from ERD to form its structure.

A very basic but key difference between the ERD and Relationship schema is that an ERD must necessarily identify unique entities by underlining the entity name. In the relationship schema, this need not be the case – the unique entity might or might not be differentiated(in some cases, we see that they might be written in bold or italics)

In my position in the Sport Industry Research Center, we have data on six different organizations and they are all listed in different files. We would need to join all six tables together to get overall values for the Philadelphia Youth Sport Collaborative (PYSC). Then when we would make individual reports for each organization, we need to combine all six organizations data to get an overall value of the PYSC groups to compare to each individual organization. Note that when we combine the organizations, we leave out the respective group we are making a report for so that the PYSC groups just contain the other five organizations.

At work, we book trades done in mutual funds into the accounting system. When doing so, we need to crossreference trade information with specific codes needed for the system. For example, we have a trade that has “goldman” or “goldmanXXX” as broker, which then needs to get mapped to a specific code for the system. So, we essentially build a table of trade details and then feed in broker codes by crossreferencing.

My understanding of the difference between a schema and a ERD involves the practice of actually diagramming out the information contained within the database. My understanding of the purpose of the schema is to act as a definition of the rules and information that will populate the database and govern how the information will enter the database. The ERD acts as a diagram that shows the relationships that govern the database. So an ERD is a diagram that governs the relationships, while the schema is the mathematical concept of how the database is governed.

Question: Describe a situation where you’d need to use a join to retrieve information from a set of tables?

An example of a situation requiring a join of two tables would be trying to figure out what the length of the rental is for each customer who has rented a car in a car rental company database. In this case, we would have to join the ‘Customer’ table with the ‘Rental’ question in order to answer the question via a query in MY SQL.

One situation where you would need to join tables to retrieve information would be if you wanted to match up students with teachers based on class. If you simply joined table students and teachers together, the teachers would be related to different pairings of students based on class.

Q2:

I believe the basic difference between schema and ERD is the purpose of usage. Schema is used as a pool to draw data, whereas ERD is a graphical presentation as how different entities in this data are related to one another.

In my past job I was to find relationships between customers demographic information, product attributes, and territory sales locations. First, one must join customer and product tables to determine what customers bought and how many of each product they bought. Then join the territory data to determine in which regions each customer resides and the volume of products sold in that region. This is ideal for determining sales trends and spending patterns. That way the company could better predict future sales by region per customer. There were a number of other joins involved and the more joins one adds the more in-depth ones analysis will be.

Question 1:
A situation where you would have to join tables to retrieve information would be if you wanted to know how many nurses per bed in hospitals throughout the country, specifically for hospitals with over 500 beds. You would have to join a table of hospitals throughout the country, with information on beds per hospital, and cross that with employment data on nurses per hospital. I currently am tackling this challenge as part of our Fox Management Consulting course.

2) ERD is just a representation of various entities and relationships between them. Whereas, an actual database contains schema. Moreover, ERD just shows the how different entities should be related; whereas schema contains actual tables along with relationships between them.

Question 1:
When I worked in media research, we used a join to retrieve data all the time. For example, if ESPN wanted to know how many males between the ages of 18-34 who lived in the Chicago DMA watched Sportscenter at 11:00PM every night of the week, we had to join 18-34 males with the Chicago DMA and ESPN Sportscenter at 11:00PM to come up with the number of viewers.

Team members:

1. Shriya Garvalia (Shriyagarvalia@temple.edu)
2. Anshuman Jha (Anshuman.jha@temple.edu)
3. Sindhuja Narra (Sindhuja.narra@temple.edu)
4. Karan Parikh (Karan.parikh@temple.edu)

The project we have chosen deals with inaccuracy of data in the Enterprise Resource Planning (ERP) software for ABC Ltd (named changed for security reason). During one of our team members summer internship experience, he found that ERP software is prone to errors and duplication.
In our project, we would analyse the issues with duplication and errors related to data and come up with a feasible solution.

Team members:

Shashank Agrahara Nagaraj – tuf28621@temple.edu
Patrick Burgi – tuf27756@temple.edu
Lauren Patterson – tuf02801@temple.edu
Jennifer Sargrad – tuf33273@temple.edu
Gaurav Varma – tuf29514@temple.edu

We will study how a business to consumer marketing organization could identify opportunities by combining two different databases: a transactional database of customer purchases and a profile database of customer demographics.

Team:
Alex Guilford (alex.guilford@temple.edu)
Asher Halbert (asher.halbert@temple.edu)
Mark Ramacciotti (mark.ramacciotti@temple.edu)

Topic: how NFL player conduct, in terms of arrests/ fines/ suspensions, affects ticket sales and how this can translate to a standard or protocol for reprimanding players in order to maximize ticket sales.

Team:
Greg Ebbecke – tue83901@temple.edu or greg.ebbecke@gmail.com
Ryan Lucera – ryan.lucera@gmail.com
Matt Cohan – tue87167@temple.edu

We have identified critical inconsistencies in how client and corporate information is collected at an advertising agency. This leads to ineffective collaboration on projects due to unnecessary information duplication.

Our project will focus on mapping the relationships between key stakeholders within the agency, developing standards to facilitate communication and collaboration between stakeholders and ultimately empowering stakeholder decision-making through better access to internal information.

Goda Neeharika Damera ,
Julian C. Wallace” ,
Robert Dettore ,
Nina Codeacova ,
Kartik Sridhar Rao

Use the pertinent BI techniques to analyzing existing customer and transaction data to come up with sound conclusions by aligning our strategy based on our analysis.

Team Members:
Goda Neeharika Damera – neeharika.damera@temple.edu
Julian C. Wallace – tuf20099@temple.edu
Robert Dettore – tuf31200@temple.edu
Nina Codeacova – nina.codeacova@temple.edu
Kartik Sridhar Rao – kartik.sridhar.rao@temple.edu

Use the pertinent BI techniques to analyzing existing customer and transaction data to come up with sound conclusions by aligning our strategy based on our analysis.

My recommendation to ensure an ERD accurately represents a particular event is to make sure it accounts for the dynamic nature of events. When designing a database, if it represents the state of things as they are today, it may not accurately represent the state of things tomorrow. Building in the ability to change the structure based on new information would be a beneficial design. In addition, I would recommend being very clear in your attribute descriptions to ensure that the correct data is captured in the appropriate place. For example, if you have a attribute “phone number,” this data could represent the phone number of many entities, so making sure you have clear descriptors is key.

I would consider a completed ERD to be knowledge because it integrates information, context, and insight. The information part of the ERD is the “how our business process works” at the time at which the model was created. The ERD also has insight into what types of relationships exist between the entities and what attributes are characteristics of the entities based on the context of the ERD model.

For an ERD to correctly represent the business event, the assumptions and requirements should be correctly understood. If our assumptions are incorrect , the ERD will be an incorrect representation. For example, if we think the relationship between two entities is ‘one to many’ instead of ‘many to many’ , we will end up missing a table when we build a database from ERD. Also, as discussed in class, the context is important to derive the necessary attributes and define relationships between different entities.

I think an ERD would be a form of data and information. The knowledge would be present in the database and the reports that are generated.

I see the ERD as being knowledge since it’s a map of a process. It’s more than just data and information because there are relationships and associations built into it that make it a map. I think those characteristics make this knowledge.

Q2:
I would consider an ERD a form of information. It contains data, which is interrelated and integrated into a system providing information for the end-users. The end-users, however, may derive knowledge from the information they are extracting from the database. Knowing how to use specific queries, relevant information may be extracted that can be further used to make some data-driven conclusions that represent knowledge.

Advice for an accurate ERD: Before beginning the process of formulating your ERD take a step back and analyze your business situation from a macro view. Ask yourself, “What do I truly want to understand given the data at my disposure?” Familiarize yourself with the key economic drives essential for commercial success and test those against industry standards / norms. As ERD represents a systematic process for Data to Knowledge acquisition, it is important that you begin with the proper inputs before moving forward.

A key component for successfully designing an ERD system is to incorporate outside stakeholder perspective prior to committing to any details. This will help lend additional detail in how stakeholder information might be communicated into the ERD while also ensuring individual bias is minimized. While final decisions need to remain with the internal group, including other stakeholders creates a sounding board through which the process can be audited.

Q2:
I agree with most of you who say ERD is information. Information is defined as capturing data. ERD represents the data in a concise form that is easier to understand. However, in order to understand ERD there does not require any specific analytical or cognitive abilities. For those of us who are familiar with the ERD structure and terms, understanding ERD becomes real easy. Any decision taken based on this ERD is knowledge since it is based on both data and analytical abilities.

I would consider an ERD information. It contains data in a very organized and ready to use fashion and can be easily converted into knowledge. But, it is not knowledge by itself. An outsider to the organization could not look at the ERD and determine the best course of action. Knowledge still must include experience and an innate feel for the industry.

ERD helps to understand the organization’s data needs and serves as a schema diagram for the required system’s database. Therefore, I would say ERD is information. However, it will be considered as knowledge when it is processed further to make a decision.

One of the key attributes of an ERD is the relationship between entities, especially the role of the key attribute and the foreign key. The creation of tables and their relationships was seen by me as the most important part of creating such models. The mapping of several entities together is what converts this data into knowledge and a wrong mapping of entities can lead to knowledge being represented wrongly. This is one aspect to be carefully studied before creating an ERD.

To make sure an ERD represents the business event it is supposed to capture I think it is important to thoroughly understand all of the entities at play. Information solicitation techniques such as stakeholder interviews, observing the business event, or employee workshops would be a great way to make a MECE list of entities in the business event. Without all of the entities, it would become a lot easier to overlook a relationship or attribute that is important to track/understand.

For ERD to work efficiently, its important to correctly define the attributes , then its important to identify the relationships, be it one to many or many to many and finally its important to note and identify the primary keys or unique identifiers. I think assigning more importance to any one variable will lead to wrong assumptions. The model is based on a series of dependencies and those attributes should be correctly identified.
To you your second question, each variable in itself is a data point on the ERD and together they generate some information, for us to assign the tag of knowledge we must then make ERD more fluid and adaptable with a series of changing scenarios.

2) I would consider ERD as information. ERD gives us an idea about how and in what manner different entities are related. Also, the characteristics of each of the entities. So, overall it is a very useful tool in the understanding the whole framework on the database. Someone can just look at a complete ERD and understand business logic. Also, ERD tells us how a change in one entity affects other related entities. Therefore, ERD is definitely information.

ERD diagram though gives an idea of the data in the organization, we can tell the relationship between different entities and hence it would give us the information about what is going on in between different entities and their attributes and their dependency in an organization.

When thinking about how an ERD model accurately depicts your business, you first need to evaluate your business strategy from top to bottom. Make sure your company is aligning its strategy with its overall organizational goals and then use the ERD model to more efficiently organize your business structure. I would say that one needs to lay out all aspects of the business from the product to the services used to deliver to the prospective client. There needs to be the correct entities and attributes as well as the correct relationships between each entity. Once this is done and everything is rightfully placed, a company can gauge a better understand of the overall business needs.

I would say a completed ERD model is considered information. Data is then extracted from the information in the ERD model. You cannot collect data through an ERD model relationship because the data is within the ERD model. It does not present data, it presents a relationship within information using data.

When thinking about how an ERD model accurately depicts your business, you first need to evaluate your business strategy from top to bottom. Make sure your company is aligning its strategy with its overall organizational goals and then use the ERD model to more efficiently organize your business structure. I would say that one needs to lay out all aspects of the business from the product to the services used to deliver to the prospective client. There needs to be the correct entities and attributes as well as the correct relationships between each entity. Once this is done and everything is rightfully placed, a company can gauge a better understand of the overall business needs.

I consider ERD diagram as a a source of knowledge. It not only sheds light on data but also gives the interrelationship between various entities. Also, the relationship dependent attributes are also explained. This gives a holistic picture of the whole system.

I would consider an ERD an example of a mix of the three – data, info, knowledge. The information is needed to build the ERD example and from it we can interpret the data which forms knowledge on the subject matter. By essentially drawing the map, we can better understand the relationships amongst different entities and how one effects another.

I consider the ERD model a form of information from which a business manager could draw knowledge. The model takes data, which in its simplest form could be a list of entities with each one’s attributes listed under, along with a short explanation of the relationship to other entities, and translates the data into a visual diagram. This diagram encapsulates the data as information that describes the entities and relationships of a particular system. With a sophisticated perspective on the ERD model, a manager could analyze this visual information and convert that to knowledge of the relationships, which he/she could then combine with other knowledge of the system to provide wisdom to the other stakeholders involved in the system.

It is imperative that the business event is clearly described and articulated in order for the ERD model to work. It is crucial that the details are presented in a manner that are easy to follow and that the relationship between the entities and attributes can be followed. Without clarity, a detail can be lost that will alter the relationship. Definition of the entities and attributes and the relationships determine the ERD model. This is how the ERD model can ensure it clearly depicts the business event.

I would classify an ERD as a form of Information in that, it takes data points (entities/attributes) and organizes them as they relate to one another (relationships). An ERD stops just short of knowledge because an ERD is simply architecture. In order to take the next step to knowledge, the ERD needs to be put to use and insights drawn from its design. Only then will there be knowledge drawn from an ERD.