Software Design PDF

Chapter 4 SOFTWARE DESIGN The activities carried out during the design phase (called as design process) transform the SRS document into the design document. Figure 4.1: The design p...

Chapter 4 SOFTWARE DESIGN The activities carried out during the design phase (called as design process) transform the SRS document into the design document. Figure 4.1: The design process. Figure- A two part design process Conceptual design answers : ✓ Where will the data come from ? ✓ What will happen to data in the system? ✓ How will the system look to users? ✓ What choices will be offered to users? ✓ What is the timings of events? ✓ How will the reports & screens look like? 1 Prepared by Dr. Ali Aliedani Technical design describes :  Hardware configuration  Software needs  Communication interfaces  I/O of the system  Software architecture  Network architecture  Any other thing that translates the requirements in to a solution to the customer’s problem.  Software designers do not arrive at a finished design immediately. They develop design iteratively through number of different versions. The starting point is informal design which is refined by adding information to make it consistent and complete as shown in the figure below: 4.1 OVERVIEW OF THE DESIGN PROCESS general model of design process: A- ARCHITECTURAL DESIGN  Identifying the sub-systems and establishing framework for sub-system control and communication.  Activities necessary for architectural designing; o System Structuring o Control modeling o Modular decomposition  The output of the architectural design process is: an architectural design document. B- ABSTRACT SPECIFICATION 2 Prepared by Dr. Ali Aliedani For each sub-system, an abstract specification of the services it provides and the constraints under which it must operate is produced. Example: C- INTERFACE DESIGN - 1 For each sub-system, its interface is designed and documented. Example: D- COMPONENT DESIGN  Services are allocated to different components and the interfaces of the components are designed.  This phase entails detailed implementation design of the interfaces that are identified in the interface design.  Services that are allocated to each subsystem are designed as to be implemented. 3 Prepared by Dr. Ali Aliedani  E- DATA STRUCTURE DESIGN  The data structures used in the system implementation are designed in detail and specified. F- ALGORITHM DESIGN  The algorithms used to provide services are designed in detail and specified. 4.2 What Is a Good Software Design? The design needs to be Correct & complete Understandable At the right level Maintainable –a design that is easy to understand:  Also easy to maintain and change. How to Improve Understandability? Use consistent and meaningful names –for various design components, Design solution should consist of: –A set of well decomposed modules(modularity), Different modules should be neatly arranged in a hierarchy: –A tree-like diagram. –Called Layering 4.3 Modularity Modularization is a technique to divide a software system into multiple discrete and independent modules, which are expected to be capable of carrying out task(s) independently. If modules are independent: –Each module can be understood separately, reduces complexity greatly. –To understand why this is so, remember that it is very difficult to break a bunch of sticks but very easy to break the sticks individually. 4 Prepared by Dr. Ali Aliedani Q\ how can we compare the modularity of two alternate design solutions? Figure 4.2 Two design solutions to the same problem A design solution is said to be highly modular, if the different modules in the solution have high cohesion and their inter-module couplings are low. 4.4 COHESION AND COUPLING Cohesion is a measure of the functional strength of a module, whereas the coupling between two modules is a measure of the degree of interaction (or interdependence) between the two modules. Figure 4.3: Module coupling High coupling makes modifying parts of the system difficult, e.g., modifying a component affects all the components to which the component is connected. Why Functional Independence is Advantageous? Functional independence reduces error propagation. 5 Prepared by Dr. Ali Aliedani –degree of interaction between modules is low. –an error existing in one module does not directly affect other modules. Also: Reuse of modules is possible. –can be easily taken out and reused in a different program. each module does some well-defined and precise function the interfaces of a module with other modules is simple and minimal. 5.3.2 Measuring Functional Independence Unfortunately, there are no ways: –to quantitatively measure the degree of cohesion and coupling: –At least classification of different kinds of cohesion and coupling: will give us some idea regarding the degree of cohesiveness of a module. 4.3.3 Classification of Cohesiveness By examining the type of cohesion exhibited by a module: –we can roughly tell whether it displays high cohesion or low cohesion. Figure 4.4 classification of cohesion Functional Cohesion _ A and B are part of a single functional task. This is very good reason for them to be contained in the same procedure. Sequential Cohesion _ Module A outputs some data which forms the input to B. This is the reason for them to be contained in the same procedure. 6 Prepared by Dr. Ali Aliedani Communicational cohesion In this type, all the Code that uses the same data are kept together: –Reference or update the same data structure, Example: –The set of functions defined on an array or a stack. handle-Student-Data() { Static StructStudent-data; Store-student-data(); Search-Student-data(); Print-all-students(); }; Procedural Cohesion Procedural Cohesion occurs in modules whose instructions although accomplish different tasks yet have been combined because there is a specific order in which the tasks are to be completed. –certain sequence of steps have to be carried out in a certain order for achieving an objective, e.g. the algorithm for decoding a message. Temporal Cohesion _Module exhibits temporal cohesion when it contains tasks that are related by the fact that all tasks must be executed in the same time-span. Example: –The set of functions responsible for initialization, start-up, shut-down of some process, etc. init() { Check-memory(); Check-Hard-disk(); Initialize-Ports(); Display-Login-Screen(); } Logical Cohesion All elements of the module perform similar operations: –e.g. error handling, data input, data output, etc. An example of logical cohesion: –a set of print functions to generate an output report arranged into a single module. module print{ void print-grades(student-file){ …} 7 Prepared by Dr. Ali Aliedani  void print-certificates(student-file){…} void print-salary(teacher-file){…} } Coincidental Cohesion Coincidental cohesion exists in modules that contain instructions that have little or no relationship to one another. Module AAA{ Print-inventory(); Register-Student(); Issue-Book(); }; 4.3.4 Classification of Coupling Figure 4.5 The types of module coupling Given two procedures A & B, we can identify number of ways in which they can be coupled. Data coupling The dependency between module A and B is said to be data coupled if their dependency is based on the fact they communicate by only passing of data. Other than communicating through data, the two modules are independent. Stamp coupling 8 Prepared by Dr. Ali Aliedani Stamp coupling occurs between module A and B when complete data structure is passed from one module to another. class A{ // Code for class A. }; class B{ // Data member of class A type: Type-use coupling A var; // Argument of type A: Stamp coupling void calculate(A data){ // Do something. } }; Control coupling Module A and B are said to be control coupled if they communicate by passing of control information. This is usually accomplished by means of flags that are set by one module and reacted upon by the dependent module. Example: bool foo(int x){ if (x == 0) return false; else return true; } void bar(){ // Calling foo() by passing a value which controls its flow: foo(1); } External coupling External coupling occurs when two modules share an externally imposed data format, communication protocol, or device interface. This is basically related to the communication to external tools and devices. Common coupling With common coupling, module A and module B have shared data. Global data areas are commonly found in programming languages. Making a change to the common data means tracing back to all the modules which access that data to evaluate the effect of changes. 9 Prepared by Dr. Ali Aliedani  Figure 4.6 Example of common coupling Content coupling Content coupling occurs when module A changes data of module B or when control is passed from one module to the middle of another. In Figure 5.7, module B branches into D, even though D is supposed to be under the control of C. Figure 4.7 Example of content coupling example 4.5 Classification of Design Methodologies The design activities vary considerably based on the specific design methodology being used. A large number of software design methodologies are available. We can roughly classify these methodologies into procedural and object-oriented approaches. 4.5.1 Function-oriented Design The following are the salient features of the function-oriented design approach: 10 Prepared by Dr. Ali Aliedani Top-down decomposition: A system, to start with, is viewed as a black box that provides certain services (also known as high-level functions) to the users of the system. For example, consider a function create-new-library member. This high-level function may be refined into the following subfunctions: assign-membership-number create-member-record print-bill Each of these subfunctions may be split into more detailed subfunctions and so on. Centralised system state: The system state can be defined as the values of certain data items that determine the response of the system to a user action or external event. For example, in the library management system, several functions such as the following share data such as member-records for reference and updation: create-new-member delete-member update-member-record Design Notations Design notations are largely meant to be used during the process of design and are used to represent design or design decisions. For a function-oriented design, the design can be represented graphically or mathematically by the following:  Data flow diagrams  Data Dictionaries  Structure Charts  Pseudocode Data-flow diagram (DFD) it is a way of representing a flow of data through a process or a system (usually an information system). The DFD also provides information about the outputs and inputs of each entity and the process itself. 11 Prepared by Dr. Ali Aliedani  Example: Leveling DFD represent a system or software at any level of abstraction. 0-level DFD: It is also known as a context diagram. It’s designed to be an abstraction view, showing the system as a single process with its relationship to external entities. It represents the entire system as a single bubble with input and output data indicated by incoming/outgoing arrows. ATM Example 1-level DFD: 12 Prepared by Dr. Ali Aliedani  In 1-level DFD, the context diagram is decomposed into multiple bubbles/processes. In this level, we highlight the main functions of the system and breakdown the high-level process of 0-level DFD into subprocesses. Example2: Example of ATM Level 1 Notes:  Each process should have at least one input and an output  Each data store should have at least one data flow in and one data flow out.  Data store in a system must go through a process  All process in a DFD go to another process or a data store 2-level DFD: It is DFD goes one step deeper into parts of 1-level DFD. It can be used to plan or record the specific/necessary detail about the system’s functioning. 13 Prepared by Dr. Ali Aliedani Note: for more example see the following link Data Dictionary  A DFD is always accompanied by a data dictionary.  A data dictionary lists all data items appearing in a DFD: o Definition of all composite data items in terms of their component data items.  All data names along with the purpose of the data items.  Provides the team of developers with standard terminology for all data:  A consistent vocabulary for data is very important  In the absence of a data dictionary, different developers tend to use different terms to refer to the same data,  Causes unnecessary confusion Composite data are defined in terms of primitive data items using simple operators: +: denotes composition of data items, e.g  a+b represents data a together with b. [,,,]: represents selection,  Any one of the data items listed inside the square bracket can occur. o For example, [a,b] represents either a occurs or b ( ):contents inside the bracket represent optional data which may or may not appear. a+(b) represents either a or a+b {}:represents iterative data definition, 14 Prepared by Dr. Ali Aliedani  {name}5 represents five name data. {name}*represents zero or more instances of name data. =represents equivalence, e.g. a=b+c means that a represents b and c. * *:Anything appearing within * * is considered as comment. Balancing a DFD Data flowing into or out of a bubble: –Must match the data flows at the next level of DFD.  In the level 1 of the DFD, –Data item a flows into the bubble P2 and the data item e and b flow out.  In the next level, bubble P2 is decomposed. – The decomposition is balanced as data item a flows into the level 2 diagram and e and b flow out. NOTE:  Number the bubbles in a DFD:  Numbers help in uniquely identifying any bubble from its bubble number. The bubble at context level: –Assigned number 0.  Bubbles at level 1:  Numbered 0.1, 0.2, 0.3, etc When a bubble numbered x is decomposed,  Its children bubble are numbered x.1, x.2, x.3, etc. Example: Tic-Tac-Toe Computer Game  A human player and the computer make alternate moves on a 3 X3 square. A move consists of marking a previously unmarked square. The user inputs a number between 1 and 9 to mark a square Whoever is first to place three consecutive marks along a straight line (i.e., along a row, column, or diagonal) on the square wins. 15 Prepared by Dr. Ali Aliedani  As soon as either of the human player or the computer wins,  A message announcing the winner should be displayed. If neither player manages to get three consecutive marks along a straight line,  And all the squares on the board are filled up,  Then the game is drawn. The computer always tries to win a game. Data Dictionary Display=game + result move = integer board = {integer}9 game = {integer}9 result=string Commonly Made Errors  Unbalanced DFDs  Forgetting to name the data flows  Unrepresented functions or data  External entities appearing at higher level DFDs  Trying to represent control aspects  Context diagram having more than one bubble  A bubble decomposed into too many bubbles at next level  Terminating decomposition too early  Nouns used in naming bubbles 4.5.2 Object-oriented Design In the object-oriented design (OOD) approach, a system is viewed as being made up of a collection of objects (i.e. entities).  Each object is associated with a set of functions that are called its methods.  Each object contains its own data and is responsible for managing it.  The data internal to an object cannot be accessed directly by other objects and only through invocation of the methods of the object.  The system state is decentralised since there is no globally shared data in the system and data is stored in each object. For example, in a library automation software, each library member may be a separate object with its own data and functions to operate on the stored data.  Bottom-up approach 16 Prepared by Dr. Ali Aliedani   Carried out using UML 4.5.3 Object-Oriented versus Function-Oriented Design Grady Boochsums up this fundamental difference saying: –“Identify verbs if you are after procedural design and nouns if you are after object-oriented design.” In OOD: –software is not developed by designing functions such as: update-employee-record, get-employee-address, etc. –but by designing objects such as: employees, departments, etc. In OOD: –state information is not shared in a centralized data. –but is distributed among the objects of the system. Example In an employee pay-roll system, the following can be global data: –names of the employees, –their code numbers, –basic salaries, etc. In contrast, in object oriented systems: –data is distributed among different employee objects of the system. Objects communicate by message passing. –one object may discover the state information of another object by interrogating it. 4.6 Unified Modeling Language (UML) The Unified Modeling Language (UML) is a general-purpose, developmental, modeling language in the field of software engineering that is intended to provide a standard way to visualize the design of a system.  UML is a modelling language Not a system design or development methodology 17 Prepared by Dr. Ali Aliedani   Used to document object-oriented analysis and design results.  Independent of any specific design methodology.  Adopted by Object Management Group (OMG) in 1997.  OMG is an association of industries  In 2005, UML was also published by the International Organization for Standardization (ISO) as an approved ISO standard. Why are UML Models Required? o Modelling is an abstraction mechanism:  Capture only important aspects and ignores the rest.  Different models obtained when different aspects are ignored. o An effective mechanism to handle complexity. o UML is a graphical modelling technique. o Easy to understand and construct. 4.6.1 UML Diagrams Nine diagrams in UML1.x :  Used to capture 5 different views of a system: User’s view (Use Case Diagram) Structural view (Class Diagram, Object Diagram) Behavioral view (Sequence Diagram, Collaboration Diagram, State-chart Diagram, Activity Diagram) Implementation view (Component Diagram) Environmental view (Deployment Diagram)  Views: Provide different perspectives of a software system.  Diagrams can be refined to get the actual implementation of a system. 4.6.1.1 Class Diagram Class In any system, there shall be number of objects. Some of the objects may have common characteristics and we can group the objects according to these characteristics. This type of grouping is known as a class. Hence, a class is a set of objects that share a common structure and a common behavior. A class represents a set of objects having similar attributes, operations, relationships and behavior 18 Prepared by Dr. Ali Aliedani. Figure 5.8 The squire class Example: What are the Different Types of Relationships Among Classes? Four types of relationships: Inheritance Association Aggregation/Composition Dependency Inheritance Imagine that, as well as squares, we have triangle class. Figure 5.9 shows the class for a triangle. Figure 5.9 The triangle class 19 Prepared by Dr. Ali Aliedani  Figure 4.10 Abstraction common features in a new class This sort of abstraction is called inheritance. The low level classes (known as subclasses or derived classes) inherit state and behaviour from this high level class (known as a super class or base class). Polymorphism When we abstract just the interface of an operation and leave the implementation to subclasses it is called a polymorphic operation and process is called polymorphism. Encapsulation (Information Hiding) Encapsulation is also commonly referred to as “Information Hiding”. It consists of the separation of the external aspects of an object from the internal implementation details of the object. Hierarchy Hierarchy involves organizing something according to some particular order or rank. It is another mechanism for reducing the complexity of software by being able to treat and express sub-types in a generic way. Figure 4.11 Hierarchy Association Relationship Enables objects to communicate with each other: One object must “know” the ID of the corresponding object in the association. 20 Prepared by Dr. Ali Aliedani Example  A teacher teaches 1 to 3 courses (subjects)  Each course is taught by only one teacher.  A student can take between 1 to 5 courses.  A course can have 100 to 300 students Multiplicity: The number of objects from one class that relate with a single object in an associated class. Association and Link  A link: An instance of an association  Exists between two or more objects  Dynamically created and destroyed as the run of a system proceeds  For example: An employee joins an organization.  Leaves that organization and joins a new organization. 21 Prepared by Dr. Ali Aliedani  G- Aggregation Relationship Represents whole-part relationship Represented by a diamondsymbol at the composite end. Usually creates the components: Also, aggregate usually invokes the same operations of all its components. This is in contras to plain association Observe that the number 1 is annotated at the diamond end, and a * is annotated at the other end. This means that one document can have many paragraphs. On the other hand, if we wanted o indicate that a document consists of exactly 10 paragraphs, then we would have written number 10 in place of the (*). Aggregation vs. Inheritance Inheritance: Different object types with similar features. Necessary semantics for similarity of behavioris in place. Aggregation: Containment allows construction of complex objects. Composition o A stronger form of aggregation o The whole is the sole owner of its part. o A component can belong to only one whole o o The life time of the part is dependent upon the whole. o The composite must manage the creation and destruction of its parts. 22 Prepared by Dr. Ali Aliedani If any changes to any of the order items are required after the order has been placed, then the entire order has to be cancelled and a new order has to be placed with the changed item. Aggregation versus Composition: Both aggregation and composition represent part/whole relationships. When the components can dynamically be added and removed from the aggregate, then the relationship is aggregation. If the components cannot be dynamically added/delete then the components are have the same life time as the composite. public class Car{ private Wheel wheels; public Car (){ wheels = new Wheel(); wheels = new Wheel(); wheels = new Wheel(); wheels = new Wheel(); }} See the following java example Generalizations are used to show an inheritance relationship between two classes. 23 Prepared by Dr. Ali Aliedani  Dependency A dependency relation between two classes shows that any change made to the independent class would require the corresponding change to be made to the dependent class. A dependency relationship is shown as a dotted arrow that is drawn from the dependent class to the independent class. Two important reasons for dependency to exist between two classes are the following:  A method of a class takes an object of another class as an argument.  A class implements an interface class. In this case, dependency arises due to the following reason. If some properties of the interface class are changed, then a change becomes necessary to the class implementing the interface class as well. Abstract class Abstract classes cannot be instantiated, but they can be subclassed. An abstract method is a method that is declared without an implementation. Example: abstract class Shape{ abstract void draw(); } class Rectangle extends Shape{ void draw(){System.out.println("drawing rectangle");} } class Circle1 extends Shape{ void draw(){System.out.println("drawing circle");} } //In real scenario, method is called by programmer or user class TestAbstraction1{ 24 Prepared by Dr. Ali Aliedani  public static void main(String args[]){ Shape s=new Circle1();//In a real scenario, object is provided through method, e.g., getShape() m ethod s.draw(); } } 4.5.1.2 UML Object Diagram Object diagrams are dependent on the class diagram as they are derived from the class diagram. It represents an instance of a class diagram. 25 Prepared by Dr. Ali Aliedani   aggregation: is part of"  composition: is made of  dependency: Depends on Composition B is a permanent part of A  A contains B  A is a permanent collection of Bs 26 Prepared by Dr. Ali Aliedani  Subclass / Superclass A is a kind of B  A is a specialization of B  A behaves like B Association(Collaboration) A delegates to B  A needs help from B  A and B are 4.5.1.3 Interaction Diagram  Can model the way a group of objects interact to realize some behaviour. o How many interaction diagrams to draw?  Typically each interaction diagram realizes behaviour of a single use case o Draw one sequence diagram for each use case. A- Sequence Diagram Captures how objects interact with each other: To realize some behaviour (use case execution). Emphasizes time ordering of messages. Can model: Simple sequential flow, branching, iteration, recursion, and concurrency Sequence Diagram Notations 1- Lifeline A lifeline represents an individual participant in the Interaction. 27 Prepared by Dr. Ali Aliedani  2- Actor An Actor a type of role played by an entity that interacts with the subject (e.g., by exchanging signals and data). An actor can also be an external to the subject (i.e., in the sense that an instance of an actor is not a part of the instance of its corresponding subject). They typically represent roles played by human users, external hardware, or other subjects. Note That: 1. An actor does not necessarily represent a specific physical entity but merely a particular role of some entity 2. A person may play the role of several different actors and, conversely, a given actor may be played by multiple different person. 3- Activation An activation is represented by a thin rectangle on a lifeline) represents the period during which an element is performing an operation. The top and the bottom of the of the rectangle are aligned with the initiation and the completion time respectively. 4- Messages A- Call Message A call message defines a particular communication between lifelines of an interaction, which represents an invocation of operation of target lifeline. 28 Prepared by Dr. Ali Aliedani  B- Return Message A return message defines a particular communication between lifelines of an interaction, which represents the pass of information back to the caller of a corresponded former message. c- Self Message A self message defines a particular communication between lifelines of an interaction, which represents the invocation of message of the same lifeline. d- Recursive Message A recursive message defines a particular communication between lifelines of an interaction, which represents the invocation of message of the same lifeline. It's target points to an activation on top of the activation where the message was invoked from. e- Create Message A create message defines a particular communication between lifelines of an interaction, which represents the instantiation of (target) lifeline. 29 Prepared by Dr. Ali Aliedani  f- Destroy Message A destroy message defines a particular communication between lifelines of an interaction, which represents the request of destroying the lifecycle of target lifeline. g- Duration Message A duration message defines a particular communication between lifelines of an interaction, which shows the distance between two time instants for a message invocation. Each message is labelled with the message name. Some control information can also be included. Two important types of control information are:  A condition (e.g., [invalid]) indicates that a message is sent, only if the condition is true.  An iteration marker shows that the message is sent many times to multiple receiver objects as would happen when you are iterating over a collection or the elements of an array. 30 Prepared by Dr. Ali Aliedani  Sequence diagram for the renew book use case  Return Values Optionally indicated using a dashed arrow: Label indicates the return value. Don’t need when it is obvious what is being returned, 31 Prepared by Dr. Ali Aliedani  5- Sequence Fragments  UML 2.0 introduces sequence (or interaction) fragments. Sequence fragments make it easier to create and maintain accurate sequence diagrams  A sequence fragment is represented as a box, called a combined fragment, which encloses a portion of the interactions within a sequence diagram  The fragment operator (in the top left cornet) indicates the type of fragment  Fragment types: ref, assert, loop, break, alt, opt, neg 32 Prepared by Dr. Ali Aliedani  33 Prepared by Dr. Ali Aliedani  34 Prepared by Dr. Ali Aliedani  35 Prepared by Dr. Ali Aliedani This is a tutorial link explain of the ATM sequence diagram: https://www.youtube.com/watch?v=pCK6prSq8aw 36 Prepared by Dr. Ali Aliedani Database Design  Overview: 3 Level Database Design See the following table Entity-Relationship Diagrams It is a detailed logical representation of data for an organization and uses three main constructs. Entity Thing in the real world Attribute Property of an entity Most of what we store in the database Relationship Association between sets of entities Possibly with attribute(s) 37 Prepared by Dr. Ali Aliedani  A- Entity - Entities can be classified into different types. - Each entity type contains a set of entities each satisfying a set of predefined common properties. E.g. Employee, Student, Car, House, Bank Account Consider an insurance company that offers both home and automobile insurance policies.These policies are offered to individuals and businesses. B- Attributes  Each entity type has a set of attributes associated with it.  An attribute is a property or characteristic of an entity that is of interest to organization. A candidate key is an attribute or combination of attributes that uniquely identifies each instance of an entity type. Student_ID Candidate Key 38 Prepared by Dr. Ali Aliedani Composite Attributes Can be subdivided into smaller subparts All cars have a year, make, model, and registration. Multivalued Attributes Can take a [possibly specified] number of values. Ex: All cars have a year, make, model, registration, and some number of colors Example: Draw an ERD for the following description: Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations. 39 Prepared by Dr. Ali Aliedani  C- Relationships A relationship is a reason for associating two entity types. Binary relationships involve two entity types A CUSTOMER is insured by a POLICY. A POLICY CLAIM is made against a POLICY.  Relationships are represented by diamond notation in a ER diagram.  A training department is interested in tracking which training courses each of its employee has completed.  Each employee may complete more than one course, and each course may be completed by more than one employee. Degree of relationship It is the number of entity types that participates in that relationship. 40 Prepared by Dr. Ali Aliedani Relationship One-to-Many (1:N) entity instance in one entity class (parent) is related to multiple entity instances in another entity class (child) A book is published by (only) one publisher; a publisher can publish many (multiple) books  A book can be written by many (multiple) authors; an author can write many (multiple) books Minimum Cardinality Minimum cardinality describes the minimum number of instances that must participate in a relationship for any one instance Minimums are generally stated as either zero or one: (optional): participation in the relationship by the entity is optional. 41 Prepared by Dr. Ali Aliedani Example: A department controls a number of projects, each of which has a unique name, a unique number, and a single location. Exercise Draw an ERD for the following description: We store each employee’s name (first, last, MI), Social Security number (SSN), street address, salary, sex (gender), and birth date. An employee is assigned to one department, but may work on several projects, which are not necessarily controlled by the same department. We keep track of the current number of hours per week that an employee works on each project. We also keep track of the direct supervisor of each employee (who is another employee). 42 Prepared by Dr. Ali Aliedani H.W describe the following E-R Diagram 43 Prepared by Dr. Ali Aliedani Transforming E-R Diagrams into Relations – Binary 1:N Relationships Add the Primary key attribute (or attributes) of the entity on the one side of the relationship as a Foreign key in the relation on the other (N) side The one side migrates to the many side Relationship: CUSTOMER Places ORDER(s) ORDER Table BEFORE Relationship: (Order_Number, Order_Date, Promised_Date) ORDER Table AFTER Relationship: (Order_Number, Order_Date, Promised_Date, Customer_ID) CREATE TABLE ORDER( PRIMARY KEY (Order_Number), Order_Number CHAR(1), FOREIGN KEY (Customer_ID) Order_Date DATE, REFERENCES Promised_Date DATE, CUSTOMER); Customer_ID CHAR(1), 44 Prepared by Dr. Ali Aliedani Represent Relationships – Binary and higher M:N relationships Create another relation and include primary keys of all relations as primary key of new relation CREATE TABLE Manages( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) 1 Prepared by Dr. Ali Aliedani