Database Assignment Solution

This document presents a solved database assignment, covering various aspects of database design and implementation. It includes answers to questions related to conceptual schema design, the differences between database and system design, and lessons learned from creating ER diagrams. The assignment also addresses mapping ER schemas to relational schemas, the importance of constraints in SQL, and the impact of excessive JOIN operations. Furthermore, it explores data organization, data warehousing, and the benefits of business intelligence. The solutions provide insights into database concepts and practical considerations for database development.

database assignment
Student
13 December, 2016

Week 1
1. Can we have different conceptual schema design for the same application?
Answer: no we cannot have different conceptual schema design for the same
application because conceptual models the relationships between entities
used in application. No matter what, the relationships remain same. Whereas
we can use these relations differently on an external end.
2. How database design process differs from a system design process?
Answer: database design refers to the process of designing and defining the
database tables and attributes. This process requires both physical and logical
database design. It includes data dictionary, records, relationships etc. whereas
system design refers to designing the overall system which includes interface
design, architecture definition, modules, data etc. hence database design deals
only with database development whereas system design deals with defining and
designing all the parts of the system.
3. What are the lessons learned when you created your first ER-diagram?
Answer: the lessons learnt are:
a. Each entity should have a primary key.
b. Relationships should be developed between entities using foreign keys.
c. All the entities to be designed should be as per the requirement
d. Entities should be atomic.
e. The diagram should be in 3NF.
f. The multiplicity should be applied to all the relationships.
g. Each entity should have a unique name.
h. There should not be any redundant in the ERD.
i. All the attributes should be dependent on primary key.
4. Is ER-Model helpful in providing all the required information about the
application for which this conceptual schema has been designed?
Answer: the ER model provides the information about all the entities and
attributes that will be designed into a database for the application. The
conceptual design models the relationships not all the requirements of the
application. For example modules, interfaces, access rights etc. will not be
modelled in the conceptual model. Hence ER-model only represents the
database requirements which are in turned into a physical database and not all
other external and internal requirements.
1 | P a g e

Week 2
1. How helpful are the SEVEN steps provided to you for mapping ER-Schema
to a Relational schema?
Answer: these steps help in:
a. Deciding the foreign keys to setup actual relations between the tables.
b. Defining entities into tables from ER diagram to an actual relational
schema
c. Defining the type of the relationships between entities to the relationship
between the tables.
d. Designing separate tables for multivalued attributes.
e. Designing primary keys for the database tables.
f. Setting up the design of actual database tables and columns before
implementing them.
2. Is there a situation that is not covered by the SEVEN steps provided to you
for mapping ER-Schema to a Relational schema?
Answer: no there is no such situation which has not been covered by these
steps.
3. What issues you see if you have to design Relational schema without ER-
Schema?
Answer: the issues that will arise are:
a. Redundant information can be presented.
b. All attributes may not be covered.
c. All Relationships may not be covered
d. We may miss the multivalued relationships.
e. The cardinality of the relation may be wrongly presented.
f. The ternary relationship may be missed.
Week 3
1. How constraints can be enforced in SQL while implementing a given schema?
Answer: the constraints that can be implemented are:
a. Not null
b. Unique
c. Default
2 | P a g e

d. Referential integrity
e. Check constraint
2. Is it fine to use too many JOIN operations in a query?
Answer: although it depends on the type of query we are executing yet it is
not feasible to carry out too many joins because it reduces the database
performance and the output time is increased. When there are many joins all
the data has be read simultaneously and hence the performance is not
optimized.
3. What issues you see if you have to design Relational schema without ER-
Schema?
Answer: the issues that will arise are:
g. Redundant information can be presented.
h. All attributes may not be covered.
i. All Relationships may not be covered
j. We may miss the multivalued relationships.
k. The cardinality of the relation may be wrongly presented.
l. The ternary relationship may be missed.
4. How can we reduce the amount of space used by query execution?
Answer: we can setup indexes on the tables so that during the query execution,
the data is searched by the index rather than reading the entire tables and
searching the tables for required data. This way the space used will be reduced
as the data read will be lesser.
Week 4
1. Show an example data set organized in two different ways illustrating the
difference between the application-oriented and subject-oriented
organization of data?
Answer:
2. Why do some organizations prefer not to create a data warehouse? What are
other options?
3 | P a g e

Answer: some organizations do not prefer to create a data warehouse
because they lack IT resources or budget resources. So for such
organizations the other options are:
a. Data lakes
b. HTAP/In-Memory DBMS
c. OLAP-Style Analysis With Self-Service Tools
d. Business intelligence
3. What is business intelligence? Give some recent examples of BI usage.
Answer: it is a process to analyze data and gather and present actionable
information for the corporate people. It helps in making business decisions
related to growth and other developments and improvements. There are
different tools present to carry out this process. Examples of BI usage are
analysis of geographical data, improving the efficiency of supply and
delivery chain management, query reporting, risk analysis etc.
4. What BI benefits have companies found?
Answer: the benefits are:
a. Fact based decision making
b. Improved sales
c. Better risk identification hand handling
d. Information integration
e. Personalized query results for growth
f. Mapping improvement areas
g. Enhanced Information Access
h. Data visualization
i. Real time decisions for business
4 | P a g e