Miravild

Структура базы данных (SQL):

employee — сотрудник
— id
— headId — его непосредственный руководитель
— firstName — имя
— lastName — фамилия
— salary — размер зарплаты
— departmentId — отдел

department — отдел
— id
— name — название отдела

project — проект
— id
— name — название проекта

role — роль сотрудника в проекте
— id
— projectId
— employeeId
— name — название роли

Пожалуйста, спроектируйте оптимальную структуру в формате XML,
в которой можно представить данные из вышеописанной базы.

Вариант:

<?xml version="1.0"?>
<StructureDB>
  <Project>
    <Id>1</Id>
    <Name>Project 1</Name>
  </Project>
  <Department>
    <Id>1</Id>
    <Name>Department 1</Name>
  </Department>
  <Employee>
    <Id>1</Id>
    <HeadId>1</HeadId>
    <FirstName>FirstName</FirstName>
    <LastName>LastName</LastName>
    <Salary>123</Salary>
    <DepartmentId>
      <Id>1</Id>
      <Name>Department 1</Name>
    </DepartmentId>
  </Employee>
  <Role>
    <Id>1</Id>
    <ProjectId>
      <Id>1</Id>
      <Name>Project 1</Name>
    </ProjectId>
    <employeeId>
      <Id>1</Id>
      <HeadId>1</HeadId>
      <FirstName>FirstName</FirstName>
      <LastName>LastName</LastName>
      <Salary>123</Salary>
      <DepartmentId>
        <Id>1</Id>
        <Name>Department 1</Name>
      </DepartmentId>
    </employeeId>
    <Name>Role</Name>
  </Role>
</StructureDB>

Пример класса, создающего XML заданной структуры БД:

using System;
using System.Collections.Generic;
using System.IO;
using System.Runtime.Serialization;
using System.Xml.Serialization;

namespace ConsoleAppStructureDB
{

    /// <summary>
    /// 
    /// </summary>
    /// 
    [DataContract]
    [XmlRoot("StructureDB"), Serializable]
    public class StructureDB
    {
        [XmlElement("Project")]
        public List<Project> Project;

        [XmlElement("Department")]
        public List<Department> Department;

        [XmlElement("Employee")]
        public List<Employee> Employee;

        [XmlElement("Role")]
        public List<Role> Role;
    }

    /// <summary>
    ///
    /// </summary>
    /// 
    [DataContract(Name = "StructureDB")]
    public class Project
    {
        /// <summary>
        /// 
        /// </summary>
        /// 
        [DataMember]
        public int Id { get; set; }

        /// <summary>
        /// 
        /// </summary>
        /// 
        [DataMember]
        public string Name { get; set; }
    }

    /// <summary>
    ///
    /// </summary>
    /// 
    [DataContract(Name = "StructureDB")]
    public class Department
    {
        /// <summary>
        /// 
        /// </summary>
        /// 
        [DataMember]
        public int Id { get; set; }

        /// <summary>
        /// 
        /// </summary>
        /// 
        [DataMember]
        public string Name { get; set; }
    }

    /// <summary>
    /// сотрудник
    /// </summary>
    /// 
    [DataContract(Name = "StructureDB")]
    public class Employee
    {
        /// <summary>
        /// ид
        /// </summary>
        /// 
        [DataMember]
        public int Id { get; set; }

        /// <summary>
        /// его непосредственный руководитель
        /// </summary>
        /// 
        [DataMember]
        public int? HeadId { get; set; }

        /// <summary>
        /// имя
        /// </summary>
        /// 
        [DataMember]
        public string FirstName { get; set; }

        /// <summary>
        /// фамилия
        /// </summary>
        /// 
        [DataMember]
        public string LastName { get; set; }

        /// <summary>
        /// размер зарплаты
        /// </summary>
        /// 
        [DataMember]
        public decimal? Salary { get; set; }

        /// <summary>
        /// отдел
        /// </summary>
        /// 
        [DataMember]
        public Department DepartmentId { get; set; }
    }

    /// <summary>
    /// роль сотрудника в проект
    /// </summary>
    /// 
    [DataContract(Name = "StructureDB")]
    public class Role
    {
        /// <summary>
        /// ид
        /// </summary>
        /// 
        [DataMember]
        public int Id { get; set; }

        /// <summary>
        /// Проект
        /// </summary>
        /// 
        [DataMember]
        public Project ProjectId { get; set; }

        /// <summary>
        /// сотрудник
        /// </summary>
        /// 
        [DataMember]
        public Employee employeeId { get; set; }

        /// <summary>
        /// название роли
        /// </summary>
        /// 
        [DataMember]
        public string Name { get; set; }
    }

    /// <summary>
    /// 
    /// </summary>
    /// 
    class Program
    {
        static void Main(string[] args)
        {
            var ns = new XmlSerializerNamespaces();

            ns.Add("", "");

            var ListProject = new List<Project>();

            var project = new Project { Id = 1, Name = "Project 1" };

            ListProject.Add(project);

            var department = new Department { Id = 1, Name = "Department 1" };

            var ListDepartment = new List<Department>();

            ListDepartment.Add(department);

            var ListEmployee = new List<Employee>();

            var employee = new Employee { Id = 1, HeadId = 1, FirstName = "FirstName", LastName = "LastName", Salary = 123, DepartmentId = department };

            ListEmployee.Add(employee);

            var ListRole = new List<Role>();

            var role = new Role { Id = 1, Name = "Role", employeeId = employee, ProjectId = project };

            ListRole.Add(role);

            var structureDB = new StructureDB { Project = ListProject, Department = ListDepartment, Employee = ListEmployee, Role = ListRole };

            var xmlSerializer = new XmlSerializer(structureDB.GetType());

            var path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\StructureDB.xml";
            FileStream file = File.Create(path);

            xmlSerializer.Serialize(file, structureDB, ns);
            file.Close();
        }
    }
}

Tags: Oracle, SQL