Studio Baranda
Luka Leskur
Phase1
CS342
Table Of Contents
Phase1
1. Fact Finding and Data Gathering ………………………………………………………………………………4
Interviewing, Data Analysis, Data Collection
Data Collection...... ……………………………………………………………………………………….4
1.2 Introduction to the Enterprise/Organization……………………………………………………………5
1.3 Structure of the Enterprise ……………………….……………………………………………………………5
1.4 Itemized Description of Mayor Objects…………………………………..……………………………….5
1.5 Data Views and Operations for User Groups ………………………………………………………..…6
2.1 Entity Set Description …………………………………………………………………………………………..…7
Employee, Customer, WebSite, Project
2.2 Relationship Set Description …………………………………………………………………………………11
2.3 ER diagram ...... ………………………………………………………………………………………………..12
Phase 2
1. E-R model and the relational model…………………………………………………………………………13
Description
Comparison
Conversion from E-R model to relational model
Constraints
2. E-R database to relational database…………………………………………………………………………15
Employee relation
Customer relation
WebSite relation……………………………………………………………………………………………….16
Project relation
Works_on Relation…………………………………………………………………………………………….17
Requires Relation
Orders Relation
3. Relation Instances…………………………………………………………………………………………………….18
4. Queries…………………………………………………………………………………………………………………….22
Phase 3
1. Implementation of the Relational database..……………………………………………………………26
SQL
Schema Objects in Oracle
2. Schema Objects in this Project…………………………………………………………………………………27
ll_employee
ll_customer…………………………………………………………………………………………………….28
ll_project
ll_website……………………………………………………………………………………………………….29
ll_works_on
ll_requires……………………………………………………………………………………………………….30
ll_orders
3. SQL Queries…………………………………………………………………………………………………………….31
Phase 4
1. Common Features in Oracle PL/SQL and MS Transact-SQL.………………………………………36
2. Oracle PL/SQL……………………….…………………………………………………………………………………36
Layout
Stored Procedures
Stored Functions………………………………………………………………………………………………37
Trigers
3. Oracle PL/SQL Subprograms …………………………………………………………………………………….38
Phase 5
1. Graphic User Interface Design and Implementation………..………………………………………41
Daily User Activities
2. Relations, views and subprograms related to the activitie…………………….…………………41
3. Screen Shots and descriptions of our menu and display………………………………………….42
Main Menu
EmployeeMain………………………………………………………………………………………………….43
Employee …………………………………………………………………………………………………………44
4. Majors Steps of Designing a User Interface …………………….……………………………………….45
Class Descriptions
Major Features
Development Process ………………………………………………………………………………………46
Design and implementation the application
Conclusion ……………………………………………………………………………………………………….47
1.Fact Finding and Data Gathering
1.1
This step of the process in creating our database helps us to find and gather data and facts about our business, and all the important facts that relate to it so we can have easier path in creating our conceptual model. This step is very important in order to avoid any design flaws and implementation problems before even constructing our database. Furthermore the following methods were used for fact finding and data gathering.
Interviewing- This business environment is consist of only several employees, therefore it was easy to talk to each programmer and designer about the nature of their job. Furthermore, the basic picture of the working environment was created which allowed us to construct the starting diagram of the entity types and relations.
Data Analysis- Data of the working environment was provided in the digital from in which the structure of some of the project was found and the way they are processed through Studio Baranda company. This gave us general idea of how much data will be working with in our data base, and it specified the entities collected during interview.
Data Collection- One of the programmers in the company had an unfinished project of the company’s data base for maintain web sites done in Microsoft Access environment. Moreover, this helped during the starting phase of the project although the project made by one of the employees, was never used.
Techniques Use
Data collected from the Interviewing the employees and Data analysis gave a starting schema of how the entity types are going to look like, and structure of the relationships between them. This phase gave me enough information to start implementing my own information towards process of creating the database.
1.2
Introduction to the Enterprise/Organization
Baranda is a multimedia studio, founded in 2008. They are placed in Split, Croatia. Furthermore, Baranda is a team of enthusiasts with years of experience in video production, photography, motion graphics, web development and graphics design. Together they handle and maintain big projects using different skills and providing good results.
1.3 Structure of the Enterprise
Structure of this enterprise is quite simple. It consists of four programmers and three designers that work together as one team that divides projects depending on the status of the previous project. Furthermore, team of designers is the responsible for contacting the customer and estimating the project itself. Baranda designers, after getting the general idea and sketching the project, forward their work to programmers depending on the complexity, and type of the project. Programmers are responsible for contacting the customer in the case of project being related to maintenance of the following web site. The purpose of this proposed database is to organize division of work between employees, and to have complete set of characteristics of project in order to produce quality report.
1.4 Itemized Description of Major Objects
An Employee entity is the representation of the employee working on certain project. Main purpose of this function is to bring clear picture of division of qualifications in between employee’s and in which project of WebSite they are currently involved in. Moreover, major entity is the WebSite that has relation requires towards Project entity, and works_on relation towards Employee. Moreover, all the objects revolve around this central function, which is main purpose of this enterprise. Furthermore, entity Customer is representation of customer his personal data and request that goes through relationship order into the Project function. Entity Project holds the main info related to WebSite and its development.
1.5 Data Views and Operations for User Groups
As there is no manager in this enterprise, all the employees work on WebSite’s as a group. They are all equally responsible for viewing and modifying all the entities and relationships, which puts team work in between employees as one of the crucial factors for efficient work environment. Main purpose of this data base is to provide division of work among the employees and to have insight of data basic data related to maintaining and developing websites.
2.Conceptual Database Design
2.1 Entity Set Description
Employee
-This entity describes all the employees involved into the enterprise. As the purpose of this database is to provide division of labor among employees, only the basic attributes are recorded.
-Candidate Keys: employeeID, Name
-Primary Key: employeeID
-Strong/Weak Entity: Strong
-Fields to be indexed: employeeID, Name, Contact
Atributes:
Name / employeeID / Name / Contact / SkillDescription / Employee ID number / Employees full name / Employees contact / Skill that employees possesses (what type of work is he specialized in)
Domain/type / String / String / String / String
Value Range / 0…2^32 / any / any / any
Default Value / none / none / none / none
Nullable? / No / No / No / No
Unique? / Yes / Yes / Yes / No
Single or
Multiple value / Single / Single / Multiple / Single
Simple or
composite / Simple / Composite / Composite / Simple
Customer
-This entity contains information of the customer that website is for and his basic info such as name and contact.
-Candidate Keys: customerID, Name
-Primary Key: customerID
-Strong/Weak Entity: Strong
-Fields to be indexed: customerD, Name, Contact
Atributes:
Name / customerID / Name / ContactDescription / customerID number / Customers full name / Customers contact
Domain/type / String / String / String
Value Range / 0…2^32 / any / any
Default Value / none / none / none
Nullable? / No / No / No
Unique? / Yes / Yes / Yes
Single or
Multiple value / Single / Single / Multiple
Simple or
composite / Simple / Composite / Composite
WebSite
-This entity works as the main entity in this data base and it contains attributes: page, which defines webpage specifications and tribute server that defines all the necessary data for online web server, and finally webpageID attribute that helps us define which website we are handling.
-Candidate Keys: websiteID, server
-Primary Key: websiteID
-Strong/Weak Entity: Strong
-Fields to be indexed: websiteID, page, server
Atributes:
Name / websiteID / page / serverDescription / websiteID number / Web page adress / Web page server specification (domain and space)
Domain/type / String / String / String
Value Range / 0…2^32 / Any / Any
Default Value / None / None / None
Nullable? / No / No / No
Unique? / Yes / Yes / Yes
Single or
Multiple value / Single / Single / Multiple
Simple or
composite / Simple / Simple / Composite
Project
-This entity is completely depended on the WebSite entity and directly connects to and describes the WebSite entity. Furthermore, this is the most complex entity in this entity set and as mentioned before it is there to provide full description of WebSite project.
-Candidate Keys: projectID, implemantation
-Primary Key: projectID
-Strong/Weak Entity: Strong
-Fields to be indexed: projectID, Implementation
Atributes:
Name / projectID / Start_date / End_date / Price / ImplementationDescription / Projects ID number / Starting date of the project / Ending date of the project / Price of the corresponding project expressed in dollars / Information about programming interface that is going to be used for developing of website
Domain/type / String / String / String / Integer / String
Value Range / 0…2^32 / Any / Any / 0…2^32 / Any
Default Value / None / None / None / None / None
Nullable? / No / No / No / No / No
Unique? / Yes / Yes / Yes / No / Yes
Single or
Multiple value / Single / Single / Single / Single / Multiple
Simple or
composite / Simple / Simple / Simple / Simple / Composite
2.2 Relationship Set Description
Works_on:
-One of the main purposes of this database is to keep track of which employee is involved in what web site to be able to know with how much staff resources we dispose with. This relationship records number of employees involved in various website.
Mapping cardinality:n..n
Descriptive Field: none
Participation Constrain: This relationship is total participation constrain
Orders:
- Following enterprise income is one of the crucial aspects of the existence of Studio Baranda, although its small corporation with petite number of employees it makes through its orders from customers enough to sustain it self. The relationship order in between customer and project keeps track of which customer is involved in corresponding project.
Mapping cardinality:n..n
Descriptive Field: none
Participation Constrain: This relationship is total participation constrain
Requires:
-Following entity relates WebSite and Project entity in which project contains dates, prices, and type of implementation of websites that are being put to procedure of creation.
Mapping cardinality:1..n
Descriptive Field: none
Participation Constrain: This relationship is optional for Website, and mandatory for Project
2.3 E-R Diagram
n n
n n
1 n
Phase 2
E-R model and the relational model
Description
The E-R model represented in phase one is really useful for visual representation of our database and planning our data organization. Furthermore, next step in process of creating data base is converting E-R database in a relational database to our project purposeful for data manipulation. The relational model for database management was first formulated and proposed in 1969 by Edgar “Ted” Codd, British scientist while he was working for IBM. Moreover, this model is based on first-order predicate logic, and it allows us to express all data as the set of mathematical relations.
Comparison
The ER model is an abstract and conceptual representation of data. Moreover it helps us to create visual representation of our database as a plan of data flow between entities and their relations. This step in creating our database serves as illustration of all possible entities, attributes, relation, and cardinality which is finest way to focus on actual design of the database and providing a clearer picture to the users that are not familiar with more technical further steps of implementing the database.
The relational model is less visual than ER model. This model has all the entities and attributes mentioned in ER model but layout is more technical, as entities designed as tables with attributes in the table columns. Moreover, this model is based on mathematical relations and fist-order predicate logic. The relational model represents the database as set of relations, and those relations look like values in our table. This table is usually called relation schema. The relations schema has a relation name, attributes (columns of the table), and rows of the table are records or tuples. The relations model is not as visual appearing as ER model but it has more accurate description of tuple entries and constrains which we miss in ER model.
Conversion from E-R model to Relational Model
In the conversion to relational model it is important to start from the conceptual model. Moreover, conceptual model provides basis of relation – entity structure which requires conversion as this model is not suitable for implementing to actual functioning database. Furthermore, relational model is designed in the way that makes implementing to actual working database possible.
Converting process from E-R model to relational model is consisted of several steps which include entity, relation, and attribute conversion. Furthermore, we start the conversion process by converting previously made strong entity type in E-R model to relation with its previously defined simple attributes, and we take simple component attribute from composite attribute. Secondary we convert week entities into relations by adding primary key of primary entity type to the week entity. Moreover, we convert 1 to 1 relationship set to the relation schema. For this step there are three methods apposite to use:
-Foreign key approach: including primary key of one relation as a foreign key
in the other.
-Merged relation approach: merging two entity types and the relationship into a single relation
-Cross- reference or relationship relation approach: creating extra relation that holds primary keys to each relation. This step is most commonly used for mapping of N:N relationship types
Moreover, in next step we do mapping of binary 1:n relationship types by choosing one the three methods previously mentioned. We chose these methods depending on the participation. Furthermore, if participation is high we generally use foreign key approach otherwise we typically use cross-reference relation approach. Mapping of binary n:n relationship uses one of the three previously mentioned approaches additionally. Final two steps are consisted of creating relation to represent multi-valued attributes and